MySQL flush query cache

Question

I have a slow database query that runs 18 seconds for the first run and 4 seconds as any subsequent run. I am trying to optimize it's "coldstart" execution time. But not able to reproduce it continuously.

select SQL_NO_CACHE directive doesn't help.

None of commands below make it run 18 seconds again:

FLUSH QUERY CACHE;
RESET QUERY CACHE;
FLUSH TABLES;

Even database restart doesn't make it run long again.

Is there any other command that flushes cache?

Table engine is InnoDB.

Try emptying innodb buffer pool as well. On the other hand, why not make the query run properly? — N.B.
– N.B., Commented Mar 7, 2013 at 9:57

Community · Accepted Answer · 2017-04-13 12:22:39Z

Let me explain why its not helping.

Your Requirement : Here you are trying to run the same query again, but you want it to perform as its running first time only by cleaning the cache.

When a query runs, there are multiple type of cache which comes into the picture. 'Query Cache' the most common cache which we talk about, then there are MySQL Caches (e.g. Innodb Buffer Pool ), table_cache (at MySQL level & at InnoDB level as well), OS Caches, Hardware Cache.

select SQL_NO_CACHE : This will prevent running query to save any 'Query cache', this means if you will run same query again that wont have any 'Query Cache', but other caches will be there in picture.

FLUSH QUERY CACHE : this just Defragment the 'query cache' to better utilize its memory

RESET QUERY CACHE : Removes all query results from the 'Query cache', this wont affect if you used 'SQL_NO_CACHE', in all previous queries.

In addition to what you are trying, Few more things you can try to Avoid these caches.
MySQL Restart is must for 'MySQL Caches' (Innodb Buffer Pool), there is no other way.
set global key_buffer_size=0; to make key buffer size zero
set global query_cache_type=0;
set global query_cache_size=0;
OS level Cache : this post may help you

newtover · Accepted Answer · 2013-03-07 09:58:40Z

0

I usually add a user-defined variable to the query, so that it would skip the query cache. Not sure though, that you could emulate a cold start in such a way.

SELECT a.* 
FROM a, (SELECT @a:=NULL) as foo;

answered Mar 7, 2013 at 9:58

newtover

32.2k11 gold badges89 silver badges89 bronze badges

Collectives™ on Stack Overflow

MySQL flush query cache

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related