Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL_NO_CACHE does not work

Tags:

sql

mysql

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) 
like image 490
Koerr Avatar asked Jul 12 '11 15:07

Koerr


People also ask

What is Sql_no_cache in MySQL?

SQL_NO_CACHE. 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.

Why is MySQL query cache deprecated?

The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines. We considered what improvements we could make to query cache versus optimizations that we could make which provide improvements to all workloads.

What is no cache in SQL?

Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

How do I flush MySQL query cache?

You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache. The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.


2 Answers

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.

like image 75
Will Demaine Avatar answered Sep 21 '22 13:09

Will Demaine


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.

like image 27
Vladislav Vaintroub Avatar answered Sep 21 '22 13:09

Vladislav Vaintroub