MySQL Workbench much faster than Python for the same query

Question

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;

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

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

Answer 1

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

1. Python has to start, whereas you already have workbench running.
2. Python has to load your program, whereas workbench doesn't.
3. Python has to open a connection to the database whereas workbench (I assume) already has one.

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