MySQL - force not to use cache for testing speed of query

Question

I'm testing the speed of some queries in MySQL. The database is caching these queries making it difficult for me to get reliable results when testing how fast these queries are.

Is there a way to disable caching for a query?

System: MySQL 4 on Linux webhosting, I have access to PHPMyAdmin.

Thanks

score 597 · Answer 1 · edited Feb 10 '17 at 08:13

597

Try using the SQL_NO_CACHE (MySQL 5.7) option in your query. (MySQL 5.6 users click HERE )

eg.

SELECT SQL_NO_CACHE * FROM TABLE

This will stop MySQL caching the results, however be aware that other OS and disk caches may also impact performance. These are harder to get around.

edited Feb 10 '17 at 08:13

RJ Anoop

763
13
26

answered Oct 08 '08 at 09:09

Jarod Elliott

15,460
3
35
34

7

[Documentation](http://dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html) – Samuel Katz Oct 23 '11 at 20:38
6

A nice article about mysql Query cache. How to setup and see the cache in action! Worth the read. http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs-Query-Cache.htm – Adrian P. Feb 20 '14 at 18:03
3

Before trying to improve performance, try restarting your mysql server. It may be that some process is affecting everything. It happened with me. Although it is a comment that has no direct connection with the issue, it can help many people. – deldev Dec 09 '14 at 16:15
3

This stops it from caching the results but does it also stop it from *using* the cache? – Brett Mar 18 '15 at 15:53
4

@Brett see SalmanPK's comment way back in '11. That doc page is literally 4 sentences and answers your question directly. In case the page is removed in future: "It neither checks the query cache to see whether the result is already cached, nor does it cache the query result." – maurice Jul 15 '15 at 19:02
In order to avoid system caches, this is what I use, unfortunately it involves a service restart, but on a test system, this is usually feasible: `service mysql stop ; sync ; echo 3 > /proc/sys/vm/drop_caches; service mysql start` – marc82ch Jun 29 '16 at 09:37
1

DOCS: `The server` **`does not use the query cache`** `. It ` **`neither checks the query cache`** `to see whether the result is already cached, ` **`nor does it cache the query result`** `. (Due to a limitation in the parser, a ` **`space character must precede and follow the SQL_NO_CACHE`** `keyword; a nonspace such as a newline causes the server to check the query cache to see whether the result is already cached.)` – jave.web Jul 03 '16 at 11:33
1

As Jarod said, other caches affect this too much. I have a query that takes 3 minutes. Even if I use SQL_NO_CACHE and run FLUSH TABLES, it always takes under 2 seconds thereafter. I normally have to wait several hours before it's slow again which makes optimising queries a very slow process. – wcndave Sep 13 '16 at 07:45
@wcndave, just wondering if you ever found a way around that...? – Anish Ramaswamy Apr 16 '18 at 23:25
3

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. – Urasquirrel Jul 12 '19 at 17:32

score 138 · Answer 2 · answered Jul 15 '11 at 12:07

138

Another alternative that only affects the current connection:

SET SESSION query_cache_type=0;

answered Jul 15 '11 at 12:07

John Carter

53,924
26
111
144

My bad. It was `query_cache_size` I was looking at, not `query_cache_type`. I was mixing up yours and SeniorDev's answer. – Mike Apr 23 '13 at 03:55
Neither of those options seem to work in MySQL 8 ... ? – Josh M. Jul 21 '23 at 12:48

score 73 · Answer 3 · answered Jun 20 '11 at 08:19

73

Any reference to current date/time will disable the query cache for that selection:

SELECT *,NOW() FROM TABLE

See "Prerequisites and Notes for MySQL Query Cache Use" @ http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

answered Jun 20 '11 at 08:19

mediobit

731
5
2

score 32 · Answer 4 · answered Mar 24 '11 at 07:45

There is also configuration option: query_cache_size=0

To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. If you build MySQL from source, query cache capabilities can be excluded from the server entirely by invoking configure with the --without-query-cache option.

See http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

score 26 · Answer 5 · answered Oct 10 '08 at 06:51

26

You can also run the follow command to reset the query cache.

RESET QUERY CACHE

answered Oct 10 '08 at 06:51

djt

461
3
5

14

This is probably overkill... ideally, you only want MySQL to temporarily ignore the cache. – BMiner Sep 21 '11 at 16:24
4

You also need special permissions for this. – Erick Robertson Feb 03 '14 at 18:55

score 21 · Answer 6 · answered Aug 11 '09 at 00:47

21

One problem with the

SELECT SQL_NO_CACHE * FROM TABLE

method is that it seems to only prevent the result of your query from being cached. However, if you're querying a database that is actively being used with the query you want to test, then other clients may cache your query, affecting your results. I am continuing to research ways around this, will edit this post if I figure one out.

answered Aug 11 '09 at 00:47

wbharding

4,213
2
30
25

1

But if your cache is filled up by others, you will get the illusion of being fast. But sometimes not. – karatedog Sep 04 '15 at 09:38

score 15 · Answer 7 · answered Nov 01 '13 at 12:45

15

I'd Use the following:

SHOW VARIABLES LIKE 'query_cache_type';
SET SESSION query_cache_type = OFF;
SHOW VARIABLES LIKE 'query_cache_type';

answered Nov 01 '13 at 12:45

Sergio Costa

610
6
14

score 5 · Answer 8 · answered Jan 14 '12 at 13:20

5

Using a user-defined variable within a query makes the query resuts uncacheable. I found it a much better indicator than using SQL_NO_CACHE. But you should put the variable in a place where the variable setting would not seriously affect the performance:

SELECT t.*
FROM thetable t, (SELECT @a:=NULL) as init;

answered Jan 14 '12 at 13:20

newtover

31,286
11
84
89

4

"I found it a much better indicator than using `SQL_NO_CACHE`." How so? Seems you would need a pretty strong case for using an obscure hack over an explicit keyword, unless the explicit keyword isn't doing what it claims. – Air Jun 02 '14 at 15:53
1

@Air I found this solution to work better than SQL_NO_CACHE as well. SQL_NO_CACHE worked for the first time running the query, but then after that it did not work again. I'm assuming this is due to other mechanisms of caching. I think this works better because a search that relies on a variable can't be cached by any layer or mechanism--at least, that's my best guess. – Klik Apr 16 '15 at 13:36
Upvoted. For me, `SQL_NO_CACHE` or `/*!40001 SQL_NO_CACHE */` made no difference at all compared to regular query. However I could see an increase of query duration time of 50% by using `, (SELECT @a:=NULL)`. Anyways, This does not avoid the OS/Disk cache. See Ian's answer. Still, a worthy improvement. Thanks! – Julio Sep 07 '21 at 13:11

Ian · Answer 9 · 2020-12-02T13:14:03.617

Whilst some of the answers are good, there is a major caveat.

The mysql queries may be prevented from being cached, but it won't prevent your underlying O.S caching disk accesses into memory. This can be a major slowdown for some queries especially if they need to pull data from spinning disks.

So whilst it's good to use the methods above, I would also try and test with a different set of data/range each time, that's likely not been pulled from disk into disk/memory cache.

score 2 · Answer 10 · answered Mar 17 '09 at 12:53

2

If you want to disable the Query cache set the 'query_cache_size' to 0 in your mysql configuration file . If its set 0 mysql wont use the query cache.

answered Mar 17 '09 at 12:53

Jinesh

801
4
13
22

score 0 · Answer 11 · answered Dec 15 '21 at 21:23

You must change SQL string. Because SQL string is a cache key. For example, add a timestamp to a SQL comment.

Function for PHP:

function db_RunSQL($SQL, $NoCacheMode=false)
{
$SQL = (($NoCacheMode) ? '/*'.time().'*/ ' : '') . $SQL;
return mysqli_query(db_SavedConnect(), $SQL);
}

MySQL - force not to use cache for testing speed of query

11 Answers11

Linked