Disable the MySQL query cache to debug the average or general query execution time.

Find in the tutorial 4 different ways to disable the cache ether temporary, per session or permanently.

The problem

To execute the first query it takes 29 seconds.

Minutes later the exact same query is executed and it takes less than a second.

mysql> select * from mytable where 1 limit 1;
1 row in set (29.64 sec)
mysql> select * from mytable where 1 limit 1;
1 row in set (0.00 sec)

Disable query cache at server start

To disable the query cache when the MySQL server is started, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. If you build MySQL from source, query cache capabilities can be excluded from the server entirely by invoking configure with the –without-query-cache option

Sample configuration (my.cnf)

Change if exist, otherwise add variables:

query_cache_limit  = 1M
query_cache_size  = 16M

To:

query_cache_limit = 0
query_cache_size = 0

Before:

mysql> select * from mytable where 1 limit 1;
1 row in set (29.75 sec)
1 row in set (0.00 sec)

After:

mysql> select * from mytable where 1 limit 1;
1 row in set (29.75 sec)
1 row in set (29.68 sec)

Inject NO-CACHE in SELECT

This way works always but your queries must be manipulated. If it’s just on query you may do it manually, but otherwise you may have to change the connector class/function of your application/framework.

PHP example:

preg_replace(“^SELECT”, “SELECT SQL_NO_CACHE”, $QUERY)

Before:

mysql> select * from mytable where 1 limit 1;
1 row in set (29.84 sec)
mysql> select * from mytable where 1 limit 1;
1 row in set (0.00 sec)

After:

mysql> select SQL_NO_CACHE * from mytable where 1 limit 1;
1 row in set (29.19 sec)
mysql> select SQL_NO_CACHE * from mytable where 1 limit 1;
1 row in set (29.97 sec)

Clear Query cache before running the query

This option will work only once. The cache clearing must be executed before each time you like to test your query.

mysql> RESET QUERY CACHE;
mysql> select * from mytable where 1 limit 1;
1 row in set (29.67 sec)
mysql> select * from mytable where 1 limit 1;
1 row in set (0.00 sec)
mysql> RESET QUERY CACHE;
mysql> select * from mytable where 1 limit 1;
1 row in set (29.57 sec)

Disable cache in MySQL connection session

This way works only in the current connected session. This will not work from shared or web application where the connection may change. Therefore this is only useful if you can execute it from the console like in these examples.

Before:

mysql> select * from mytable where 1 limit 1;
1 row in set (30.10 sec)
mysql> select * from mytable where 1 limit 1;
1 row in set (0.00 sec)

Change cache setting:

mysql> SHOW VARIABLES LIKE 'query_cache_type';
| query_cache_type | ON    |
mysql> SET SESSION query_cache_type = OFF;

After:

mysql> select * from mytable where 1 limit 1;
1 row in set (30.20 sec)
mysql> select * from mytable where 1 limit 1;
1 row in set (33.00 sec)