Testing Query Speed

Question

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, but I need to know what is the real speed of my queries.

• Can I reset the caching after the query instruction, or something like that?
• In the developement stage, what are you using to see the speed of your queries?
• Can you provide me a useful resource/tool/tutorial/blog to read about this thing/feature/problem?

BTW I'm using Apache, MySQL and PHP in Windows (XAMPP).

Answer 1

Personally, I tend to use the 2nd and subsequent executions as my benchmark when developing hence this answer...

Arguably the real speed is with caching of data and execution plan.

If it's run often enough to matter, then it will be cached in real life. You are measuring performance the 2nd time. For the first run, you're measuring load/compile times.

On a production server data will be cached too because of use, but on development it won't be.

It it's run once a day or as an occasional report then it doesn't matter (within reason).

Answer 2

You may want to impose a stress test environment to get query results to be as real time as possible. For example, the MyISAM Key Buffer (size governed by key_buffer_size) by default is 8MB, and the minimum value is 8 (that's right, 8 bytes). It is responsible for holding index pages from .MYI files. Set this value to 8 and every keyed lookup in MyISAM must be read over and over again.

This would also work in prinicple with InnoDB. The default InnoDB Buffer Pool Size (governed by innodb_buffer_pool_size) is 128MB and minimum is 1MB (in MySQL 5.5). It is responsible for holding data and index pages from .ibd and/or ibdata1 files. Set this value to 1M and every keyed lookup in InnoDB (which always includes an additional lookup in the clustered row index (gen_clust_index)) must be read over and over again.

For added stress, set the query_cache_type to 0 to force queries not to be cached.

Just add those three(3) minimum values into /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=1M
key_buffer_size=8
query_cache_type=0

and restart MySQL.

Doing this should make all queries perform at its bare minimum best (or worst) every time.

Give it a Try !!!