MySQL - force not to use cache for testing speed of query

Question

Try using the SQL_NO_CACHE (MySQL 5.7) option in your query. (MySQL 5.6 users click HERE )

eg.

SELECT SQL_NO_CACHE * FROM TABLE

This will stop MySQL caching the results, however be aware that other OS and disk caches may also impact performance. These are harder to get around.

Another alternative that only affects the current connection:

SET SESSION query_cache_type=0;

Any reference to current date/time will disable the query cache for that selection:

SELECT *,NOW() FROM TABLE

See "Prerequisites and Notes for MySQL Query Cache Use" @ http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

There is also configuration option: query_cache_size=0

To disable the query cache at server startup, 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.

See http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

You can also run the follow command to reset the query cache.

RESET QUERY CACHE