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

Solution 1

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.

Solution 2

Try using SELECT BENCHMARK(times, query)

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

Solution 3

Let's assume that:

1. you do not use persistant connection
2. the database installed on the server where the stats are done (no network connection)
3. nobody else is using the database (row/table locks)
4. no other heavy process is running
5. etc....

If you really want to benchmark your query, you have to do the following:

$database->query('SET SESSION query_cache_type = OFF');

Then you run the query 2-3 times in a loop (to "warm up" the server).

And only then:

$database->query('FLUSH STATUS'); #If you use the stats to profile your query

$t = microtime(TRUE);
$fp = $sub->generateFingerprint();
echo microtime(TRUE)-$t;

$database->query('SHOW STATUS');

Et voila!! :)))

BTW, query speed is one of the parameter to read. Learn how to read the very precious information returned by SHOW STATUS and EXPLAIN .... This will be a lot better.

Here is a link you will love: http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/

Enjoy. :)

Comments

[user34814]

Only scalar expressions can be used. Although the expression can be a subquery, it must return a single column and at most a single row. For example, BENCHMARK(10, (SELECT * FROM t)) will fail if the table t has more than one column or more than one row.