MySQL 5.1 Reference Manual :: 7 Optimization :: 7.5 Optimizing the MySQL Server :: 7.5.4 The MySQL Query Cache :: 7.5.4.2 Query Cache SELECT Options

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

Previous / Next / Up / Table of Contents

User Comments

Posted by [name withheld] on August 24 2005 5:58pm

[Delete] [Edit]

Note: Using SQL_NO_CACHE does not bypass the cache mechanism, so it is not a reliable way to time query speed. All it does is tell the server not to cache the results of the specific query you're sending.

If you've already executed the query without the option, some other time, that cached result will be fetched.

Posted by Oli Sennhauser on July 10 2006 1:36pm

[Delete] [Edit]

I do not agree with the comment above because of 2 reasons:
1.) The hash is different when I have SELECT SQL_NO_CACHE * ... and SELECT * ... So the result will never match.
2.) See test sequence below:
mysql> select count(*) from City c1 join City c2 on c1.name = c2.name;

+----------+
| count(*) |
+----------+
|     4271 |
+----------+

1 row in set (7.98 sec)

mysql> select count(*) from City c1 join City c2 on c1.name = c2.name;

+----------+
| count(*) |
+----------+
|     4271 |
+----------+

1 row in set (0.00 sec)

mysql> select sql_no_cache count(*) from City c1 join City c2 on c1.name = c2.name;

+----------+
| count(*) |
+----------+
|     4271 |
+----------+

1 row in set (8.04 sec)

mysql> select sql_no_cache count(*) from City c1 join City c2 on c1.name = c2.name;

+----------+
| count(*) |
+----------+
|     4271 |
+----------+

1 row in set (8.46 sec)

Add your own comment.

Top / Previous / Next / Up / Table of Contents