php - When to use SQL_NO_CACHE

Question

I am in the process of going over my queries, and I have been reading articles about how you should use SQL_NO_CACHE in SELECT queries. This has confused me because every article in the end has a different conclusion on when to use this. One blog I have read said you should use this if you have identical queries, and are unique. On another blog I read that you should use it when you have to extract information that never changes.

Can someone explain to when is a good practice to use this? I know it's been asked before but reading a lot of articles didn't help, especially when people are saying to use this method in different situations. I made some theoretical situations, can someone tell me if it's beneficial to use SQL_NO_CACHE. Thank you and I do apologize for a repeated question. I'm just really confused.

1. Say a website store its configuration (i.e. site name, site description, keywords), and on every page a query request is made to extract this information as it's required on every page.

2. You select a userID during a log in check, the query only runs during the log in check process.

3. You select some data from table a in order to update a field in table b, should you use SQL_NO_CACHE on the select for table a?

Thank you.

Answer 1

SQL_NO_CACHE

---

Simply add SQL_NO_CACHE after the SELECT part of the SELECT statement and before the fields list. The first query below will use the query cache if it is enabled and the query is cached:

SELECT * FROM table WHERE search= 'keyword'; //lets take 1ms

The second query below will not use the query cache:

SELECT SQL_NO_CACHE * FROM table WHERE search= 'keyword'; //lets take ~0.2ms at 2nd time

This is particularly useful when benchmarking a query; if the query cache is enabled although the first query may take some time the second and subsequent queries are almost instant. With the use of SQL_NO_CACHE you can be assured the query cache is not used and can safely compare result times. The SQL_NO_CACHE hint turns off MySQL's builtin query caching mechanism for a particular query. You can help MySQL make the query cache more efficent by using this hint on queries that are highly dynamic (such as a keyword search, or a report that only runs nightly). Make sure query caching is turned on otherwise there is no need for this command.

what SQL_CACHE and SQL_NO_CACHE ?

The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache. SQL_CACHE tells MySQL to store the result in the query cache if it is cacheable and the value of the query_cache_type system variable is 2 or DEMAND. With 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. (Due to a limitation in the parser, a space character must precede and follow the SQL_NO_CACHE keyword; a nonspace such as a newline causes the server to check the query cache to see whether the result is already cached.)

NO_CACHE according to my opinion can be used if 'CACHE' is enabled and the data in the db is dynamically updated , ie db data cache can't be relied upon , eg: storing user password hash we cant rely on CACHE since there is frequent possibility of a change of data

Updates of useful scenarios

---

1) force not to use cache for testing speed of query