Reduced query time from 90 to 0.3 seconds

Kevin Day, June 20th, 2007

Recently, I had a PHP script that used a MySQL query that joined three tables and then ordered the result by the index of the first table. It ran fine for a few weeks, but then one day the query started taking 90 seconds to run. Using MySQL’s EXPLAIN statement, I found it was reading from disk instead of memory, and that was probably causing the big slow-down.

Oddly, when I tried running the query in phpMyAdmin, the query ran in under a second, so it was hard to debug. But later when I ran the query from the MySQL console, it took 90 seconds just like the PHP script. I tried a few different versions of the query to make it run faster, and luckily all I had to do was drop the ORDER BY clause and the query time dropped to 0.3 seconds. The table was already sorted in the right order, so that clause wasn’t even necessary. I had just thrown it in there because I felt better explicitly specifying how it should be sorted. Guess I’ll know better next time.

I was surprised that the ORDER BY clause could make such a big difference. My guess is that the result (about 700 rows) got stored in a temporary table on the disk before it was sorted. However, I was even more surprised that phpMyAdmin gave a different query time than the console. Not sure why there would be a difference there.

2 comments (tags: databases, mysql)