Why Is Python Oursql Slower Than MySQLdb?

I will confess to being a Python newbie, so apologies for any ignorance express or implied. Last week while investigating the root cause of slow MySQL queries in a Python application, I decided to run a side by side comparison of the Oursql versus MySQLdb modules. To my surprise, queries run using Oursql were twice as slow as those run in MySQLdb.

In diagnosing, I noticed the following Oursql code in cursor.execute(self, query, params=(), plain_query=False):

try: stmt.prepare(query) except Error, e: ... else: stmt.execute(*params)

This means that by default, if you either: a) pass parameters to cursor.execute, or b) do not pass parameters but fail to explicitly specify plain_query as True, Oursql will create a prepared statement for you.

While dynamic SQL goes through the parse, plan and execute phases on each execution, prepared statements allow you to perform the parse and plan once, and then reuse this statement across multiple executions. Prepared statements are great for optimizing the performance of frequently used SQL statements if you actually keep the statements around.

As best as I can tell, Oursql appears to be creating and discarding prepared statements on each call in order to (gulp) avoid dealing with the string interpolation of arguments. As a result, it is often twice as slow in executing queries as MySQLdb.

Anyway, just a word of warning from a Rubyist to his Python cousins. ;)