MySQLdb 对于大型结果集非常慢

发布于 2024-11-06 21:38:51 字数 884 浏览 0 评论 0原文

我在 phpMyAdmin 和 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查询花费了848ms（甚至没有获取结果）。

蟒蛇代码：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我也只是我 2024-11-13 21:38:51

如果您希望 SQL 查询有一个大型结果集，然后计划逐条记录地迭代该结果集，那么您可能需要考虑使用 MySQLdb SSCursor 而不是默认游标。默认游标将结果集存储在客户端，而 SSCursor 将结果集存储在服务器中。与默认游标不同，如果您需要做的只是逐一迭代记录，SSCursor 不会产生较大的初始延迟。

您可以在此处找到一些有关如何使用 SSCursor 的示例代码。

例如，尝试：（

import MySQLdb.cursors

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

其余代码可以保持不变。）

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

回复收藏 0 原文