对于相同的查询，MySQL Workbench 比 Python 快得多 [英] MySQL Workbench much faster than Python for the same query

问题描述

MySQL Workbench 中的以下查询需要 0.156 秒才能完成:

The following query in MySQL Workbench takes 0.156 second to complete:

SELECT 
    date, time, minute_price_id 
FROM 
    minute_prices 
WHERE 
    contract_id = 673 AND 
    TIMESTAMP(date, time) >= '2013-05-15 13:15:00' AND 
    TIMESTAMP(date, time) <= '2015-02-23 13:15:00'
LIMIT 1000000;

虽然在 Python 中使用 mysql.connector 执行相同的查询需要 3.3 秒以上:

While the same query in Python using mysql.connector takes over 3.3 seconds:

import mysql.connector
con = mysql.connector.connect(...)
cur = con.cursor()

sql = \
    """
    SELECT 
        date, time, minute_price_id 
    FROM 
        minute_prices 
    WHERE 
        contract_id = 673 AND 
        TIMESTAMP(date, time) >= '2013-05-15 13:15:00' AND 
        TIMESTAMP(date, time) <= '2015-02-23 13:15:00'
    """
cur.execute(sql)
cur.fetchall()

我希望 MySQL Workbench 比 Python 快，因为需要传输数据，但要快 20 倍?有什么线索吗?谢谢你.

I expected MySQL Workbench to be faster than Python because data needs to be transferred, but 20x faster? Any clue? Thank you.

推荐答案

我能想到几个理由来解释这个:

There are a few reasons I can think of to explain this:

1. Python 必须启动，而您已经在运行工作台.
2. Python 必须加载您的程序，而 Workbench 则不需要.
3. Python 必须打开一个到数据库的连接，而 Workbench(我假设)已经有一个.

为了解决这个问题，请尝试使用 timeit python 模块(或手动计时)并且只包含 execute/fetchall 命令.

In order to sort this out, try using the timeit python module (or do manual timing) and only include the execute/fetchall commands.

这篇关于对于相同的查询，MySQL Workbench 比 Python 快得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！