SQL_NO_CACHE does not work

Question

The first time I run this sql, needs 39 seconds,when I run again and increase SQL_NO_CACHE,does not seem to take effect:

mysql> select count(*) from `deal_expired` where `site`=8&&`area`=122 && 
endtime<1310444996056;
+----------+
| count(*) |
+----------+
|      497 |
+----------+
1 row in set (39.55 sec)

mysql> select SQL_NO_CACHE count(*) from `deal_expired` where `site`=8&&`area`=
122 && endtime<1310444996056;
+----------+
| count(*) |
+----------+
|      497 |
+----------+
1 row in set (0.16 sec)

I tried a variety of methods, here

and even restart the mysql server or change table name, but I still can not let 39 seconds run this SQL

I replaced another SQL, and an increase in the first run on SQL_NO_CACHE, the problem is the same:

mysql> select SQL_NO_CACHE count(*) from `deal_expired` where `site`=25&&`area`=
134 && endtime<1310483196227;
+----------+
| count(*) |
+----------+
|      315 |
+----------+
1 row in set (2.17 sec)

mysql> select SQL_NO_CACHE count(*) from `deal_expired` where `site`=25&&`area`=
134 && endtime<1310483196227;
+----------+
| count(*) |
+----------+
|      315 |
+----------+
1 row in set (0.01 sec)

What is the reason? How can I get the same SQL run-time?

I want to find a way to optimize this SQL to perform 39 seconds

BTW: RESET QUERY CACHE FLUSH QUERY CACHE FLUSH TABLES SET SESSION query_cache_type=off does not work

mysql state cache has been closed:

mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.04 sec)

mysql> select count(*) from `deal_expired` where `site`=25&&`area`=134 && endtime<1310
483196227;
+----------+
| count(*) |
+----------+
|      315 |
+----------+
1 row in set (0.01 sec)

mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

explan this SQL,used site+endtime composite index(named site_endtime):

mysql> explain select count(*) from `deal_expired` where `site`=8&&`area`=122 && endti
me<1310444996056;
+--------+------+-------------------------------+--------------+---------+------
-+------+-------------+
| table  | type | possible_keys                 | key          | key_len | ref
 | rows | Extra       |
+--------+------+-------------------------------+--------------+---------+------
-+------+-------------+
| deal_expired | ref  | name,url,endtime,site_endtime | site_endtime |       4 | const
 |  353 | Using where |
+--------+------+-------------------------------+--------------+---------+------
-+------+-------------+
1 row in set (0.00 sec)

@Karolis has a site+endtime (named `site_endtime`) composite index — Koerr, Jul 12 '11 at 16:29
I don't know anything about your particular database, but it seems that it would be a good idea to create another composite index (site+area+endtime). MySql will read less rows, so it should become faster. — Karolis, Jul 12 '11 at 16:42
@Karolis,thanks, I will create `expired` boolean field(0 and 1),and change my SQL query to: `site=?&&area=?&&expired=1` and create site+area+expired composite index, is this better than `site=?&&area=?&&endtime — Koerr, Jul 12 '11 at 17:10
I think there should be no much difference between (site+area+endtime) and (site+area+expired). But you can test it. — Karolis, Jul 12 '11 at 18:19

score 46 · Answer 1 · answered Jan 18 '13 at 00:08

46

The first query should use SQL_NO_CACHE to tell MySQL not to put the result into the cache. The second query uses the cache and the tells MySQL not to cache the result of that query, which does nothing.

tl;dr - Reverse your queries.

answered Jan 18 '13 at 00:08

Will Demaine

1,516
1
11
12

7

I don't understand why this was not selected as correct answer. It should be. – brooNo Feb 18 '13 at 21:03
8

According to [MySQL docs](http://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html) `SQL_NO_CACHE` on second SELECT should skip checking query cache, so the above should not be needed:"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." – Nikita Sep 28 '14 at 15:58
6

**This answer is not correct**, because a query with `SQL_NO_CACHE` can also never be served with a response from the query cache. The query cache is checked *before* the SQL parser runs, using the *exact bytes* of the query for matching. Unless the response to a query made up of the exact same bytes is already in the cache, there's no possible result to use... and since the result from a query with `SQL_NO_CACHE` will never be written to the query cache, it can never be served from the cache. `SQL_NO_CACHE` is thus guaranteed to behave as OP expects, never serving a response from the cache. – Michael - sqlbot Oct 23 '16 at 05:00
1

It didn't work for me, the first query with SQL_NO_CACHE took 2 minutes, the second time also using SQL_NO_CACHE took 15 seconds – Jorge P. May 19 '17 at 05:18

score 19 · Accepted Answer · answered Jul 12 '11 at 15:27

19

The answer to "How can I get the same SQL run-time?" is - you cannot. If your query reads some rows, they are cached, dependent on the storage engine in use, those rows are either in OS cache (myisam), or in buffer pool (innodb). If rows are cached, running the same query second time is much faster, because MySQL does not have to read from the disk.

answered Jul 12 '11 at 15:27

Vladislav Vaintroub

5,308
25
31

1

That I have no way to optimize this to run 39 seconds sql? – Koerr Jul 12 '11 at 15:38
@Zenofo, the only way to try to reproduce the "cold cache" run is to restart mysql. – nathan Jul 12 '11 at 15:48
1

Even restarting mysql might not cut it, if the index/data files are in the OS file/block cache. – nos Jul 12 '11 at 15:50
2

Typically, "optimize" means run faster :) I do not seen many people asking for a way to run slower. If you're using MyISAM, you may be out of luck, but if with Innodb on Linux innodb_flush_method=O_DIRECT should avoid caching in the filesystem. – Vladislav Vaintroub Jul 12 '11 at 16:15
5

On Linux, you can also do `echo 3 > /proc/sys/vm/drop_caches` to clear the OS cache. – Michael Mior Jul 12 '11 at 16:29
5

SQL_NO_CACHE to tell MySQL not to put the result into the cache. So you got expected results only if you use SQL_NO_CACHE since first execution of a specific query. So Mysql NEVER find results in cache – realtebo Aug 05 '13 at 15:18
1

realtebo. It is not about whether MySQL find result in a specific "query cache". This is about this - there are so many caches on so many levels, that it is virtually impossible to tell mysql to run a query second time as slow as it was run first time. – Vladislav Vaintroub Aug 14 '13 at 14:39

score 10 · Answer 3 · answered Dec 29 '11 at 15:31

I was under the impression that including any sort of SQL function that is calculated in the current runtime would not cache. Have you tried doing something like the following?

select count(*), now() from `deal_expired` where `site`=8&&`area`=122 && endtime<1310444996056;

score 0 · Answer 4 · answered Jul 12 '11 at 15:26

0

see: http://forums.mysql.com/read.php?24,225286,225468#msg-225468
you could try RESET QUERY CACHE (you need the RELOAD privilege) although having just read the link above this will probably not work either :(

answered Jul 12 '11 at 15:26

Kevin Burton

11,676
2
24
37

2

I tried, `RESET QUERY CACHE` and `FLUSH TABLES`, `SET SESSION query_cache_type=off;` does not work – Koerr Jul 12 '11 at 15:40

SQL_NO_CACHE does not work

4 Answers4

Linked