How to detect if MySQL query is from cache?

Question

Is there any method to find out if an executed query is retrieved from MySQL query cache?

Of course... there are a lot of methods to count the number of cached queries on general level (SHOW STATUS LIKE '%qcache%' etc), but I want to know specifically if the current executed query is loaded from MySQL cache.

For example, in PHP the function mysql_insert_id(); returns the last inserted ID in a separate query.

In that direction, it would be a beauty to call a metadata function like mysql_is_query_from_cache($previous_query); to verify that the previous query results are actually retrieved from MySQL query cache

Any ideas on this?

Answer 1

For MySQL 5.0 and higher you can use SHOW PROFILES to get a list of recent query IDs and then SHOW PROFILE FOR QUERY %id_here% to see if there was a reference to cached data.

This is explained in more details at http://www.dbasquare.com/2012/04/03/was-a-query-served-from-mysql-query-cache/ along with some other ways around.

Answer 2

Thank you @yuriy, I've made a codesample with your 'SHOW PFOFILE' suggestion and it works like a charm! The 'SHOW PROFILE FOR QUERY 2' command gives a very pretty result with "sending cached result to client", that was exactly the trigger I was looking for! :)

/* enable profiling */
$result = mysql_query('SET profiling = 1');

/* is profiling ON for this session? */
$result = mysql_query("SHOW VARIABLES LIKE '%profiling%'");

/* execute main query */
$result = mysql_query('SELECT COUNT(*) FROM orders');

/* show overview of current profiles */
$result = mysql_query('SHOW PROFILES');

/* show profiling stats for main query */
$result = mysql_query('SHOW PROFILE FOR QUERY 2');

http://dbm.home.xs4all.nl/mysqlshowprofile2.jpg