0

I'm trying to optimize a query but need to make sure the query cache is cleared properly for obvious reasons, however it doesn't seem to be working.

I ran a query the first time and it took about 3.5 seconds and all additional times it took approx 0.25 seconds; however I made some changes to it and now want to test it again without relying on the cache.

I ran:

RESET QUERY CACHE;

...and then reran the query, but it doesn't seem to have had much affect as the execution times I am getting on the first run are approx 0.35 seconds and once again approx 0.25 seconds on any additional runs.

Is there another way to do this or do you think the first ever run was an outlier?

1 Answer 1

2

To bypass the query cache you need to change your SELECT to SELECT SQL_NO_CACHE.

This will allow you to properly benchmark your query performance.

Be aware, that this does NOT affect the InnoDB buffer pool. Whether or not the pool is populated will also affect performance.

A good rule of thumb is to run one query (with SQL_NO_CACHE), discard the result, and then run the query 3 more times to get a good idea of how fast the query is with the buffer pool populated.

3
  • It is an older codebase and is using MyISAM; is there anything extra I need to be concerned about with that like with InnoDB?
    – Brett
    May 16, 2018 at 19:19
  • @Brett Unfortunately I don't have experience with MyISAM. However, looking at the documentation, it appears it has a cache similar to InnoDB, at least in terms of the net effect on performance: dev.mysql.com/doc/refman/5.5/en/buffering-caching.html May 17, 2018 at 13:56
  • Only once more should suffice. MyISAM works the same -- but uses the 'key_buffer' and the OS's free space (instead of the buffer_pool). Times like 0.001s indicate the Query Cache; 0.25s does not. A ratio of about 10 (3.5/0.25=14) usually indicates cold vs warm cache (buffer_pool/key_buffer).
    – Rick James
    May 28, 2018 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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