title: Loading mysql table into python takes a very long time compared to R
body: <p>I have a fairly large mysql table, about 30M rows, 6 columns, about 2gb when loaded into memory.</p>
  
  <p>I work with both python and R.  In R, I can load the table into memory and it takes about 90 seconds.  But in python it takes 40 minutes.</p>
  
  <p>I've tried it both with sqlalchemy and with plain pymysql.  The code is straightforward, for example, with sqlalchemy:</p>
  
  <pre><code>db_engine = sqlalchemy.create_engine("mysql+pymysql://user:pass@host/database")
  cnx = db_engine.connect()
  table = pd.read_sql('select * from my_table',cnx)
  cnx.close()
  </code></pre>
  
  <p>Without sqlalchemy:</p>
  
  <pre><code>cnx = mysql.connector.connect(**db_details)
  cursor = cnx.cursor()
  cursor.execute('select * from my_table')
  table = pd.DataFrame(data=list(cursor),columns = cursor.column_names)
  cnx.close()
  </code></pre>
  
  <p>Either way, it is much, much slower than R, which doesn't make much sense to me.  Why is this, and is there any way to speed it up?  Even a hack would do.</p>
  
  <p>To add, pandas doesn't have anything to do with it taking so long.  In the second code snippet, if I just return <code>list(cursor)</code> instead of putting it  into a pandas DataFrame, it takes (basically) just as long.</p>
  
  <p>On edit:
  The DB is running on the same machine as R/Python, so everything should be identical in terms of throughput.</p>
  
  <p>In R I am using DBI, and the R code I use is (basically) this:</p>
  
  <pre><code>require(DBI)
  cnx &lt;- dbConnect(dbDriver("MySQL"),dbname="database",username="user",password="pass",host="host")
  table &lt;- dbGetQuery(cnx,"select * from my_table")
  </code></pre>
  
  <p>********  RESOLVED (MOSTLY) ********</p>
  
  <p>Thanks to helpful comments, particularly from @roganjosh, it appears that the issue is that the default mysql connector is written in python rather than C, which makes it very slow.  The solution is to use <code>MySQLdb</code>, which is a native C connector.</p>
  
  <p>In my particular setup, running python 3 with anaconda, that wasn't possible because <code>MySQLdb</code> is only supported in python 2.  However, there is an implementation of <code>MySQLdb</code> for python 3 under the name <code>mysqlclient</code>.</p>
  
  <p>Using this implementation the time is now down to about 5 minutes to read the whole table, not as fast as R, but much less than the 40 or so it was taking before.</p>