Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question

2 Answers 2

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).

share|improve this answer

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 !!!

share|improve this answer
    
+1 for giving a MySQL specific answer. –  StanleyJohns Jul 10 '11 at 4:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.