Faster Python MySQL

Question

I use mysql.connector (MySQLdb Python implementation?) to access MySQL. The transfer from a select statement from a cursor isn't that fast.

Is there a way to speed up the code?

Maybe another library? Which? (I have Windows and Python 3.1) Maybe a row retrieval different from iterating over the cursor?

Why do you believe it's the client library and not your query that makes things slow? Besides that, show some code. — ThiefMaster
– ThiefMaster, Commented Jan 17, 2012 at 13:59
Launching the same query in an SQL Browser is much faster. The code is spread but shouldn't take long. If it cannot be the SQL access but is in the Python code, then I'll work it out myself. That's not the question here. — Gere
– Gere, Commented Jan 17, 2012 at 14:06

unutbu · Accepted Answer · 2012-01-17 14:20:45Z

15

The default MySQLdb cursor fetches the entire query result at once from the server. Conversion of this data to a Python list of tuples can consume a lot of memory and time.

Use MySQLdb.cursors.SSCursor when you want to make a huge query and pull results from the server one at a time. Note, however, that when using SSCursor, no other query can be made on the connection until the entire result set has been fetched.

import MySQLdb
import MySQLdb.cursors as cursors
connection = MySQLdb.connect(
    ...
    cursorclass = cursors.SSCursor)
cursor = connection.cursor()
cursor.execute(query)
for row in cursor:
    ...

Or, use oursql, an alternative Python driver for MySQL. One of the features of oursql is that it fetchs rows lazily.

edited Jan 17, 2012 at 14:20

answered Jan 17, 2012 at 14:07

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gere Over a year ago

Thanks! Interesting tips :) I'll try to examine these possibilities.

Gere Over a year ago

The executemany tip was very helpful. Moreover I noticed that fetchall is considerably faster than cursor access! The SSCursor thing I haven't understood yet, but I'll keep that in mind :)

user5314006 Over a year ago

@unutbu Does mysql support server side query? I think mysql will send record one by one once it retrieves the record immediately send out. It won't cache the data. Otherwise, if someone wants to kill mysql server just send couple big tables SSCursor, then mysql server memory will ran out.

unutbu Over a year ago

@FengWang: Can you elaborate? I don't think I understand your question.

Collectives™ on Stack Overflow

Faster Python MySQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related