I have a table of a couple of million records. During production, we find that there is a particular type of query that is taking a few seconds to complete.
When I try to reproduce the issue on my machine which has replicated production data, I ran the exact query (as obtained from the slow query log) in the mysql client, it took a few seconds as expected. If I then press up and enter to repeat it, it takes 0.01 seconds subsequently.
I have looked up the docs to find out how to turn off caching, so that I can consistently reproduce the issue, and want to test if adding an index will help.
Here is what I tried:
RESET QUERY CACHE; FLUSH TABLES;
However, after the above commands, running the same query again still only takes 0.01 seconds.
I must be missing something. Any ideas?