MySQL flush query cache

mysql performance query-cache
Bogdan Gusiev picture Bogdan Gusiev · Mar 7, 2013 · Viewed 9k times · Source

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.

Answer

Noushad picture Noushad · May 5, 2015

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

Related questions

Why does an insert query occasionally take so long to complete?

This is a pretty simple problem. Inserting data into the table normally works fine, except for a few times where the insert query takes a few seconds. (I am not trying to bulk insert data.) So I setup a simulation …

Read More
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More