MySQL queries in python taking a lot of time, postgres queries work fine

I am using a time profiler for optimizing my python script. It turns out that mysql queries are taking lot of time in my python scripts. There are total of only 19 queries. These 19 mysql queries take over 7.44 secs as reported by cProfile.

Following is the complete script footprint, with mysql query and respective query time in secs.

$ python -m cProfile -s time myscript.py 
MYSQL Queries
SELECT column FROM table WHERE foreign_key = 1 AND somecolumn='val1'
0.378623008728
SELECT column FROM table WHERE foreign_key = 1 AND somecolumn='val2'
0.379124879837
...
SELECT column FROM table WHERE foreign_key = 1 AND somecolumn='val19'
0.377450942993

   60122 function calls (59599 primitive calls) in 7.634 CPU seconds

   Ordered by: internal time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
       20    7.440    0.372    7.440    0.372 {method 'query' of '_mysql.connection' objects}
       22    0.025    0.001    0.025    0.001 {method 'execute' of 'psycopg2._psycopg.cursor' objects}
        2    0.008    0.004    0.008    0.004 {method 'commit' of 'psycopg2._psycopg.connection' objects}
     1299    0.007    0.000    0.011    0.000 posixpath.py:59(join)
      982    0.007    0.000    0.007    0.000 {posix.lstat}
      429    0.006    0.000    0.010    0.000 text_file.py:162(readline)
        1    0.006    0.006    0.006    0.006 {psycopg2._psycopg.connect}
        1    0.006    0.006    0.025    0.025 __init__.py:18(<module>)
        2    0.004    0.002    0.007    0.004 connections.py:62(__init__)
      110    0.004    0.000    0.027    0.000 posixpath.py:344(realpath)
       20    0.004    0.000    0.004    0.000 {method 'store_result' of '_mysql.connection' objects}

...

Can anyone hel开发者_如何学编程p me figure out the reason?

Thanks

---

Probably you forgot to create an index on (foreign_key, somecolumn) resulting in MySQL needing to perform a full table scan to find the data. You can see this by running with EXPLAIN:

EXPLAIN SELECT column FROM table WHERE foreign_key = 1 AND somecolumn='val1'

---

have you tried enabling the slow queries log of mysql ?

this log file lists all the "slow" queries that the database engine executes. from this information, you can try explaining the query and see if you don't need to define a specific index on the table you are using.