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

Answer 1

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.)

Answer 2

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.