MySQLdb的结果集非常慢 [英] MySQLdb is extremely slow with large result sets

问题描述

我在phpMyAdmin&中都执行了以下查询MySQLdb(python).

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说查询花费了 2ms . 我的python代码说，使用MySQLdb进行查询需要 848毫秒(甚至没有获取结果).

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

python代码:

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

推荐答案

PHPMyAdmin对所有查询都设置了限制，因此您不会在界面中返回大型结果集.因此，如果您的查询通常返回1,000,000行，而PHPMyAdmin将其减少到1,000(或默认值)，那么当Python捕获甚至查询整个结果集时，您将不得不等待更长的处理时间.

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.

尝试在Python中设置一个与PHPMyAdmin上的限制匹配的限制以进行比较.

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

这篇关于MySQLdb的结果集非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！