How to test MySQL query speed, with less inconsistencies?

Question

I need a simple way to test SQL queries for speed. I am not to worried about hardware differences, I basically need a relative number.

This is what I've been doing with PHP (its fuzzy, but works):

// CONNECT TO DB HERE

$sub = new YomoSubscription(95,783);

$t = microtime(TRUE);

// contains the SQL db call i'm testing
$fp = $sub->generateFingerprint(); 

echo microtime(TRUE)-$t;

The PROBLEM I am having is that, sometimes on initial connect/run my test takes 1.25 sec for example. However on subsequent connects it takes 0.004 sec ... Why is this?

I'm pretty sure MySQL query cache is off in my.ini:

query_cache_size=0

Answer 1

Try using SELECT BENCHMARK(times, query)

More information: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark

Answer 2

Your first query may be slower because MySQL is actually hitting the disk on the first query, and not on the second.

Your operating system may cache files in memory as they are read; as a result, subsequent reads may not need to actually hit the disk, and will return much faster.

As a rule of thumb, I generally run a query a few times, and look for consistency. More often than not, the first run will take several times longer, while the 2nd 3rd and 4th take about the same amount of time. Those subsequent runs are probably more representative of the sort of performance you'll see on an actual production system -- since your production database should keep that data in the OS cache, while your dev system is rarely accessed.

In the end, when it comes to query performance, for the most part, you should just give it a quick pass in development, and monitor the slow query log in production to see which queries really need work.

As far as programatically running queries for performance data goes -- take several samples, and use the median. But in practice, this isn't going to be terribly representative of the actual performance issues you'll run into in production.