Prevent a query from using MySQL's query cache

Posted August 19th, 2009 in MySql

If you have MySQL's query cache enabled there may be times when you do not want a particular query to use the query cache, for example for benchmarking a particular query. This post shows how to do it.

SQL_NO_CACHE

Simply add SQL_NO_CACHE after the SELECT part of the SELECT statement and before the fields list. The first query below will use the query cache if it is enabled and the query is cached:

SELECT * FROM mytable WHERE foo = 'bar';

The second query below will not use the query cache:

SELECT SQL_NO_CACHE * FROM mytable WHERE foo = 'bar';

This is particularly useful when benchmarking a query; if the query cache is enabled although the first query may take some time the second and subsequent queries are almost instant. With the use of SQL_NO_CACHE you can be assured the query cache is not used and can safely compare result times.