MySQL allows us to create select statements with usage of SQL_CACHE and SQL_NO_CACHE options. These options affect caching of query results in the query cache. But for which queries is it better to use SQL_CACHE option and for which SQL_NO_CACHE one? Or maybe it is better doesn't use it at all?
Add a comment
|
3 Answers
Generally, you shouldn't have to use this at all. SQL_CACHE is only necessary if queries are not cached by default, which they are in the default configuration. SQL_NO_CACHE is useful if you know a particular query will not be used again in the near future, especially if the result set is large. The goal is to avoid cluttering the cache with results which won't be needed again.
2 Comments
Ross
Specifically regarding SQL_NO_CACHE - use this where caching the result doesn't create a benefit for reuse later. The reason you should do this is because if you have many queries that cache but are never used, you will slow mySQL down as it spends more and more time managing the cache uneccesarily. This becomes particularly apparent when people set really large query_cache sizes as the time to flush old entries grows with size. That is why there is option 0,1,2 to alter behavior of the query_cache in mySQL.
Pacerier
@Michael, Actually the recommended configuratino for production servers is to turn off query caching.
I use SQL_NO_CACHE when debugging query speed.
1 Comment
Ivan Peevski
When you are testing queries, it's useful to measure their execution time, without them being fetched from cache. Fetching from cache is much faster than fetching from database, so comparing results without knowing if it's from DB or cache doesn't make sense.
First of all , you need to update your my.cnf (LINUX) or my.ini (WINDOWS) file.
query_cache_type=2
if the value is 2, then it is on demand ,which means you have the available option to use SQL_CACHE on the query result that you want to cache.
if it is 0 , then Query cache is not activated.
if it is 1 , all query is cached in the database. If you do not want to cache on some particular queries, you can use SQL_NO_CACHE .
But you should note that the query cache result would be invalidated if your table gets update often.
Comments
