How to disable MySQL Query Caching

Question

I am trying to do some benchmarking of different queries on different columns but MySQL just won't let me do it. After the first execution of a query, I can never get the same execution time for that query again. For example if the query executes in 0.062 secs the first time, I can never get the same execution time for the 2nd, 3rd etc runs. It becomes either 0 seconds or something like 0.015.

I have read many posts on disabling and clearing MySQL query cache but none of them has been of any help for me.

like this SO post here

No matter what I do, MySQL seems to insist on using cached results.

I restart the MySQL Workbench then I run;

set global query_cache_type=0; set global query_cache_size=0; flush query cache; reset query cache; 

The execution time keeps showing 0 secs.

Only server variable that I haven't been able to change is "have_query_cache". Its value is "yes" and when I try to set it to "no" Workbench says it is read-only.

I also do;

set profiling=1; run my select query show profile for query 2; 

Profiling result shows this:

'starting', '0.000077' 'checking permissions', '0.000007' 'Opening tables', '0.000016' 'init', '0.000035' 'System lock', '0.000009' 'optimizing', '0.000013' 'statistics', '0.000094' 'preparing', '0.000008' 'executing', '0.000002' 'Sending data', '0.000016' 'end', '0.000002' 'query end', '0.000003' 'closing tables', '0.000005' 'freeing items', '0.000139' 'cleaning up', '0.000009' 

If I am not wrong this shows that no caches are being used right? But I stil see 0 secs. for the execution time.

Edit: The query I am running is a SELECT query using "SQL_NO_CACHE" like so:

SELECT SQL_NO_CACHE col1,now() from mytable where col2="some_value" 

(I added now() function to help me prevent query caching)

Edit2: I am using innoDB, MySQL 5.6.10

Could someone please help me cause I can't see what is going on here.

Thanks a lot!

Answer 1

This could be due to the cache of the data itself, and not the query cache.

To make sure, you can disable the query cache for a single statement by adding SQL_NO_CACHE after your SELECT statement.

Ex:

SELECT SQL_NO_CACHE field FROM table.