-EDIT- I CAN'T clear MySQL query cache by restarting server or with several queries

Question

As far as I understand I CAN'T clear MySQL query cache by restarting server.

I am trying to get the results similar to the first code block every time I run a sql

1- This is before restarting Apache and MySQL (First time with these queries):

<0.4280259609>
SELECT perma,title,body
FROM articles WHERE MATCH(title) AGAINST('emerald green bow out listeners')
LIMIT 0,30;
<0.0419809818>
INSERT DELAYED INTO searches (q, date, view) VALUES ('emerald-green-bow-out-listeners', '2012-02-09 15:18:09', 1) ON DUPLICATE KEY UPDATE date='2012-02-09 15:18:09', view=view+1;
<0.412530899>
SELECT q
FROM searches_read WHERE MATCH(q) AGAINST('emerald green bow out listeners') 
LIMIT 20;

2- This is before restarting Apache and MySQL (SECOND time with these queries):

<0.0016009808>
SELECT perma,title,body
FROM articles WHERE MATCH(title) AGAINST('emerald green bow out listeners')
LIMIT 0,30;
<0.0007231236>
INSERT DELAYED INTO searches (q, date, view) VALUES ('emerald-green-bow-out-listeners', '2012-02-09 15:23:13', 1) ON DUPLICATE KEY UPDATE date='2012-02-09 15:23:13', view=view+1;
<0.0002520084>
SELECT q
FROM searches_read WHERE MATCH(q) AGAINST('emerald green bow out listeners') 
LIMIT 20;

3- This is after restarting Apache and MySQL:

<0.0021140575>
SELECT perma,title,body
FROM articles WHERE MATCH(title) AGAINST('emerald green bow out listeners')
LIMIT 0,30;
<0.001044035>
INSERT DELAYED INTO searches (q, date, view) VALUES ('emerald-green-bow-out-listeners', '2012-02-09 15:20:14', 1) ON DUPLICATE KEY UPDATE date='2012-02-09 15:20:14', view=view+1;
<0.0018870831>
SELECT q
FROM searches_read WHERE MATCH(q) AGAINST('emerald green bow out listeners') 
LIMIT 20;

4- With SQL_NO_CACHE:

<0.0009889603>
SELECT SQL_NO_CACHE perma,title,body
FROM articles WHERE MATCH(title) AGAINST('emerald green bow out listeners')
LIMIT 0,30;
<0.0002679825>
INSERT DELAYED INTO searches (q, date, view) VALUES ('emerald-green-bow-out-listeners', '2012-02-09 15:37:23', 1) ON DUPLICATE KEY UPDATE date='2012-02-09 15:37:23', view=view+1;
<0.0008900166>
SELECT SQL_NO_CACHE q
FROM searches_read WHERE MATCH(q) AGAINST('emerald green bow out listeners') 
LIMIT 20;

5- After RESET QUERY CACHE:

<0.0009641647>
SELECT SQL_NO_CACHE perma,title,body
FROM articles WHERE MATCH(title) AGAINST('emerald green bow out listeners')
LIMIT 0,30;
<0.0002360344>
INSERT DELAYED INTO searches (q, date, view) VALUES ('emerald-green-bow-out-listeners', '2012-02-09 15:39:01', 1) ON DUPLICATE KEY UPDATE date='2012-02-09 15:39:01', view=view+1;
<0.0008797646>
SELECT SQL_NO_CACHE q
FROM searches_read WHERE MATCH(q) AGAINST('emerald green bow out listeners') 
LIMIT 20;

6- After FLUSH TABLES:

<0.0017900467>
SELECT SQL_NO_CACHE perma,title,body
FROM articles WHERE MATCH(title) AGAINST('emerald green bow out listeners')
LIMIT 0,30;
<0.0010399818>
INSERT DELAYED INTO searches (q, date, view) VALUES ('emerald-green-bow-out-listeners', '2012-02-09 15:40:19', 1) ON DUPLICATE KEY UPDATE date='2012-02-09 15:40:19', view=view+1;
<0.0017001629>
SELECT SQL_NO_CACHE q
FROM searches_read WHERE MATCH(q) AGAINST('emerald green bow out listeners') 
LIMIT 20;

After I restart computer I get similar results to the first code block.

Please note that following: I restart computer, for the first queries I get similar results to first code block. For the second queries with DIFFERENT SEARCH TERM, i.e. "ways you can choose more environmentally friendly", I get similar results to first code block AGAIN.

I want to be able to compare execution times with different table structures. So how can I properly clear the MySQL query cache ???

Answer 1

You might find that the reason its so fast even after restarting the mysql server is that the data is cached by the o/s as well (maybe to a lesser extent the disk itself). If you want to get some form of standard benchmark mysql has a benchmarking suite that you can use I believe.

Also, if you change your queries to :

SELECT SQL_NO_CACHE perma,title,body MySQL will not check for cached results in its query cache.

I'd always disregard the first execution of a query anyway - just to let the o/s and disk caches warm up, so base results on subsequent executions using the above.

Answer 2

You can use

RESET QUERY CACHE 

or, to clear the cache and close all open tables

FLUSH TABLES