pandas read_sql is unusually slow
<p>I'm trying to read several columns from three different MySQL tables into three different dataframes.</p>

<p>It doesn't take long to read from the database, but actually putting them into a dataframe is fairly slow.</p>

<pre><code>start_time = time.time()
print('Reading data from database...')

from sqlalchemy import create_engine
q_crash = 'SELECT &lt;query string&gt; FROM table1'
q_vehicle = 'SELECT &lt;query string&gt; table2'
q_person = 'SELECT &lt;query string&gt; FROM table3'
engine = create_engine('mysql+pymysql://user:password@host:port/dbasename')

print('Database time: {:.1f}'.format(time.time() - start_time))

crash = pd.read_sql_query(q_crash, engine)
print('Read_sql time for table 1: {:.1f}'.format(time.time() - start_time))
vehicle = pd.read_sql_query(q_vehicle, engine)
print('Read_sql time for table 2: {:.1f}'.format(time.time() - start_time))
person = pd.read_sql_query(q_person, engine)
print('Read_sql time for table 3: {:.1f}'.format(time.time() - start_time))
</code></pre>

<p>Output:</p>

<pre><code>Reading data from database...
Database time: 0.0
Read_sql time for table 1: 13.4
Read_sql time for table 2: 30.9
Read_sql time for table 3: 49.4
</code></pre>

<p>Is this normal? The tables are quite large-- table 3 is over 601,000 rows. But pandas has handled larger datasets without a hitch whenever I use read_csv.</p>

<p>IMO it doesn't make much sense to read up complete tables to Pandas DFs if you have them in MySQL DB - why don't you use SQL for filtering and joining your data? Do you really need <strong>all</strong> rows from those three tables as Pandas DFs? </p>

<p>If you want to join them you could do it first on the MySQL side and load the result set into single DF...</p>

<p>something similar to:</p>

<pre><code>qry = 'select p.*, v.*, c.* from vehicle v join person p on v.id = p.vehicle_id join crash c on c.id = p.crash_id where &lt;additional where clause&gt;'
df = pd.read_sql(qry, engine)
</code></pre>

python mysql pandas