Faster way to do mysql query in python

Question

There are list1 and list2, each contains 1,104,824 values

table1 has 350,000,000 rows with 3 columns: ID, name1, name2

and this is what I tried to do:

con = mdb.connect('localhost','user','password','db')
cur = con.cursor()
for i in range(1104824)
    sql ="select count(distinct(a.ID)) from (select name1 ,ID from table1 where name2 <> '"+str(list1[i])+"') as a where a.name1 = '"+str(list2[i])+"'"
    cur.execute(sql)
    data = cur.fetchone()[0]

but it is very very slow. Is there any faster way to do this query?

post the table structure and what exactly you are trying to do. Surely there might be a way that does not involve 1.1 million queries? — e4c5
– e4c5, Commented May 10, 2016 at 2:40
If ID is the PRIMARY KEY, you can probably change COUNT(DISTINCT ID) to COUNT(*). If name1,name2 is UNIQUE, you can probably get rid of ID. — Rick James
– Rick James, Commented May 10, 2016 at 4:43

Gordon Linoff · Accepted Answer · 2016-05-10 02:02:09Z

1

This is your query:

select count(distinct a.ID)
from (select name1, ID
      from table1
       where name2 <> '"+str(list1[i])+"'
      ) a
where a.name1 = '"+str(list2[i])+"'";

I would recommend writing this as:

select count(distinct ID)
from table1
where name2 <> '"+str(list1[i])+"' and
      name1 = '"+str(list2[i])+"'";

Then you can speed up the query with an index on table1(name1, name2, id) -- all three columns in that order.

Note: I would write the sql as:

    sql = """
select count(distinct ID)
from table1
where name2 <> '{0}' and name1 = '{1}'
""".format(str(list1[i]), str(list2[i]))

answered May 10, 2016 at 2:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:23:52Z

1

Seems like this would work as well with the appropriate indices:

select count(distinct id) 
from table1
where name2 <> 'Name1'
   and name1 = 'Name2'

Look into using parameterized queries though. Your query is vulnerable to sql injection and would break for names with apostrophes for example... Lots of examples out there, here's a couple: Python MySQL Parameterized Queries and https://stackoverflow.com/a/1633589/1073631

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered May 10, 2016 at 2:02

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Collectives™ on Stack Overflow

Faster way to do mysql query in python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related