Don't Waste Time With Python, MySQL, and datetime

19 Feb 2010

I've often suspected this, but now I have proof. Python's MySQLdb mapping from a "timestamp" or "datetime" to a native python datetime is slow. No, really slow. Fortunately the fix is easy.

Here's 1000 runs of a a query that is returning quite a few rows, each with two timestamp fields:

# using automatic conversion to datetime.datetime

SELECT start_time, end_time ....

2681150 function calls in 48.673 CPU seconds

# and some snippets from cProfile

  114000    1.912    0.000    9.561    0.000
/usr/lib/pymodules/python2.6/MySQLdb/times.py:91(mysql_timestamp_converter)
  114000    4.806    0.000    7.649    0.000
/usr/lib/pymodules/python2.6/MySQLdb/times.py:43(DateTime_or_None)

Yikes! That's saying 25% of my CPU is converting datetime formats??

Besides the performance problems, there other issues with automatically converting to a datetime. Sometimes, you don't need a full datetime object if you are computing, say, a difference betwen two times. Here, the conversion is completely not needed.

The other issue is that datetimes from MySQL are without a timezone, not even UTC (this is called a "native datetime"). This causes problems for some modules, so you may need to use this datetime to create identical datetime, but with a timezone. As an example, one needs to do something like this:

from dateutil.tz import *
TZ_UTC=tzutc()
TZ_EST=tzfile('/usr/share/zoneinfo/America/New_York')

def user_localtime(nativedt):
    return nativedt.replace(tzinfo=TZ_UTC).astimezone(TZ_EST)

Yuck

Let's rewrite that so we pass back the native timestamp directly to python as a int (or long)

# using UNIX_TIMPSTAMP()
SELECT UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) .....

1883150 function calls in 33.714 CPU seconds

Sweet. If you need to convert this to a datetime, no problem. datetime.datetime.utcfromtimestamp() is all you need, and will be faster than the automatic conversion.

While using UNIX_TIMESTAMP and FROM_UNIXTIME is ugly, the end result is well worth it.

Sidenotes

The MySQLdb isoformat parser could be improved a bit. See Fast ISO datetime format parsing for details. That said, it's best not doing any conversion until you absolutely need it.