MySQLdb is extremely slow with large result sets

Question

I executed the following query both in phpMyAdmin & MySQLdb (python).

SELECT *, (SELECT CONCAT(`id`, '|', `name`, '|', `image_code`)
FROM `model_artist` WHERE `id` = `artist_id`) as artist_data, 
FIND_IN_SET("metallica", `searchable_words`) as find_0
FROM `model_song` HAVING find_0

phpMyAdmin said the query took 2ms. My python code said that using MySQLdb the query took 848ms (without even fetching the results).

The python code:

self.db = MySQLdb.connect(host="localhost", user="root", passwd="", db="ibeat")
self.cur = self.db.cursor()

millis = lambda: time.time() * 1000

start_time = millis()
self.cur.execute_cmd("""SELECT *, (SELECT CONCAT(`id`, '|', `name`, '|', `image_code`)
FROM `model_artist` WHERE `id` = `artist_id`) as artist_data, 
FIND_IN_SET("metallica", `searchable_words`) as find_0
FROM `model_song` HAVING find_0""")
print millis() - start_time

If you run that query on MySQL command line, what time do you get? — dusan, May 13 '11 at 15:51
How many records are being returned? Are you _sure_ phpMyAdmin performed the query? — AJ., May 13 '11 at 15:51

score 15 · Answer 1 · edited May 23 '17 at 12:24

If you expect an SQL query to have a large result set which you then plan to iterate over record-by-record, then you may want to consider using the MySQLdb SSCursor instead of the default cursor. The default cursor stores the result set in the client, whereas the SSCursor stores the result set in the server. Unlike the default cursor, the SSCursor will not incur a large initial delay if all you need to do is iterate over the records one-by-one.

You can find a bit of example code on how to use the SSCursor here.

For example, try:

import MySQLdb.cursors

self.db = MySQLdb.connect(host="localhost", user="root", passwd="", db="ibeat",
                          cursorclass = MySQLdb.cursors.SSCursor)

(The rest of the code can remain the same.)

Or if you use DictCursor, replace it with SSDictCursor so results are returned as a list of dictionaries. — Marc Maxmeister, Nov 07 '12 at 21:57

score 5 · Accepted Answer · answered May 13 '11 at 15:49

5

PHPMyAdmin places a limit on all queries so you don't return large result sets in the interface. So if your query normally returns 1,000,000 rows, and PHPMyAdmin reduces that to 1,000 (or whatever the default is), then you would have to expect a lot longer processing time when Python grabs or even queries the entire result set.

Try placing a limit in Python that matches the limit on PHPMyAdmin to compare times.

answered May 13 '11 at 15:49

Mikecito

2,053
11
17

I figured it out but didn't saw your answer... However I will accept yours :P – Zippo May 13 '11 at 15:53

MySQLdb is extremely slow with large result sets

2 Answers2

Linked