MySQL Workbench is much faster than Python for the same query.

Question

The following query in MySQL Workbench takes 0.156 seconds 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;

While the same query in Python using mysql.connector takes 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()

I was expecting the MySQL Workbench to be faster than Python, because the data should be transferred, but 20 times faster? Any clue? Thank.

Answer 1

There are several reasons I can explain this:

• Python should start while you already have a desktop.
• Python should load your program, but workbench should not.
• Python should open a connection to the database, while there is already one in the workbench (I assume).

, timeit python ( ) execute/fetchall.