Question: 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?

Answer: 
You can tell the server not to place the result in the query cache by including [SQL\_NO\_CACHE](http://dev.mysql.com/doc/refman/5.1/en/query-cache-in-select.html) in the query:

```
SELECT SQL_NO_CACHE id, name FROM customer;

```

Aside from the query cache though, there's a lot more going on inside MySQL to speed things up, it caches other information about tables and indexes to speed up future queries. The first execution of the query will also warm up operating system file caches too.

What you really need to do is [EXPLAIN](http://dev.mysql.com/doc/refman/5.1/en/explain.html) the query, and look at the number of rows the database engine needs to analyse. By exploring how it uses your table indexes (or not) you will be better informed as to what indexes might be missing, or alternative ways of structuring the query.
Once the table is loaded into memory, the query should take drastically less time. If the production box takes a long time to run the query every time, I'd start looking at memory usage. If you find that your production box is swapping a lot, I'd look at upgrading its RAM.