CODEWITHSUNDEEP
mysqlcaching
Eugene Manuilov
Eugene Manuilov

Reputation: 4361

MySQL best practices to use SQL_CACHE and SQL_NO_CACHE

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?

Upvotes: 12

Views: 13494

Answers (3)

xyonme
xyonme

Reputation: 395

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.

Upvotes: 0

Michael Mior
Michael Mior

Reputation: 28752

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.

Upvotes: 13

Chris Parker
Chris Parker

Reputation: 101

I use SQL_NO_CACHE when debugging query speed.

Upvotes: 5

Related Questions