How to avoid mysql caching

Question

I have a table of several million records. During production, we find that there is a certain type of request that takes a few seconds.

When I try to reproduce the problem on my machine that was replicating production data, I executed the exact query (obtained from the slow query log) in the mysql client, it took a few seconds as expected. If I then press and enter to repeat it, it will take 0.01 seconds.

I looked through the docs to find out how to turn off caching so that I can play this issue sequentially and want to check if adding an index helps.

Here is what I tried:

RESET QUERY CACHE; FLUSH TABLES;

However, after executing the above commands, re-executing the same request will only take 0.01 seconds.

Something is missing for me. Any ideas?

Answer 1

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;

In addition to the query cache, much more is done in MySQL to speed up work; it caches other information about tables and indexes to speed up future queries. The first query execution also heats up the caching of operating system files.

, EXPLAIN , . , ( ), , , .

Answer 2

, . , , . , , .

Answer 3

has_query_cache , :

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

MySQL , .

. mysqld. , query_cache_. 5.1.3 " " , .

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

Answer 4

, , , , .

, .

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

.

Also make sure that you explain the original request, this will help you determine what is wrong faster than most things.