how to avoid mysql caching

I have a table of a couple of million record开发者_JAVA技巧s. 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?

You can tell the server not to place the result in the query cache by including SQL_NO_CACHE 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 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.

The have_query_cache server system variable indicates whether the query cache is available:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

When using a standard MySQL binary, this value is always YES, even if query caching is disabled.

Several other system variables control query cache operation. These can be set in an option file or on the command line when starting mysqld. The query cache system variables all have names that begin with query_cache_. They are described briefly in Section 5.1.3, “Server System Variables”, with additional configuration information given here.

http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

If you're unable to turn it off you could invalidate the query by issuing a simple set of update statements swapping around a peice of data that'd have been selected by the query.

So if you did.

update table set column = tmpval where id = 100;
update table set column = originalval where id = 100;

Then when you query again it'll take a while.

Also make sure you do an explain on the original query, it'll help you diagnose what's wrong faster than most things.