By default the query cache is not enabled because it does add more overhead, more memory is used and it does take processing time to check the query cache, if the query cache is checked frequently and matches are rarely found then there is no need to use the query cache, it could hurt performance instead of helping it. So if you think that users are using the same queries over and over again you can turn this option on and then test thoroughly, in some cases it will produce amazing performance improvements.
To make queries faster to respond is to use caching, a cache stores frequently used data in a place that is local where is can be accessed faster.
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
–Select statements with the following properties are not cached
use of non-deterministic functions
most subqueries
use of user-defined functions
use of temporary tables
select statements in stored functions, triggers, views and events
using lock in share mode or for update
result sets larger than the query_cache_limit (1MB by default)
—fragmentation of the query cache, there are two causes of fragmentation
the first cause is that the result sets do not always fit exactly into a block of memory and thus have to be split up
the second cause is the invalidation of queries which causes the deletion of queries in the cache leaving memory holes after the deletion
using sql_no_cache
—-MySQL recommend a maximum query cache size of 256MB.
defraging the cache
re-order the query cache
flush query cache
empty the query cache
reset query cache
