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)

SQL_NO_CACHE does not work

4 Answers