« When will I Settle on A Planner? | Main | An Objective for OSCON 2004 »

July 21, 2004

Impressed at Speed of MySQL's 'load data infile'

We store images in a database. Over the past week I've been doing some load testing to get performance statistics for my OSCON presentation, using the binary_data table as a source for getting information to drive the load tests. After getting into some of the tests I thought it would be good to have an idea of the image size distribution, average image size etc. Unfortunately every query against the table is slow because we're dealing with records as big as 5Mb, and using length(binary_data) to determine image size for each record doesn't help speed things up.

I decided to dump the pieces of information I needed to a textfile for processing elsewhere. Tried OpenOffice and Excel, neither of which can handle such a large set of numbers (Excel's limit is 65536 records). Then it hit me that a separate table in MySQL was probably the best way to store this data.

I used MySQL's load data infile, fully expecting it to take a bit of time churning. With indexes on it took 8.99 seconds. Curiosity forced me to drop the table and create it without indexes . . . 1.29 seconds to load 513,549 records. Queries that were taking over 10 minutes are now in the fractions of seconds.

For the curious:
+-------------+-------------+---------------+
| image_count | total_bytes | average_bytes |
+-------------+-------------+---------------+
| 513549        |11817103519|       23010.66 |
+-------------+-------------+---------------+

Posted by mike at July 21, 2004 5:46 AM