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) 

Answer 1

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.

Answer 2

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.