MySQL query cache is one of the option on MySQL server optimization. But question here is, is it really good to do MySQL query Caching?
To understand this we have to know what really happens during MySQL query caching.
Example:
Select * from table ;
Above is the result of running a simple query on 2M rows, with query cache enabled. Here we see each time query is executed, Query cache is checked for existing result. If result not found, new cache is written to the query cache. Which seems good but imaging the different scenario.
1. Each time there is modification in the table like update, edit on column e.t.c query that is cached is invalid, so whole process of reading the query and writing to query has to perform again.
2. When query cache is written, its locks the cache of that query, so if one query is changing anything to the cache, no other query can not access it unless the lock is released, which results more response time. If there is higher frequency of updates on the query there will be more response time.
3. If you have millions of combination of query that can be generated in your database, caching query is not an option, as caching is only for few sec and holding millions of cached query to the memory, is not a possible options.
So, this summarized that query cache is not always good so when we use it ?
1. if we have same query is written in same piece of code very frequently, then there is a less chance table is updated with in that milli- second, in that case we can use query caching. Many framework related development of highly oops based development we are using same query many time in the code for example:
$result = $dataModel->find->id(‘5’);
// php code
if($dataModel->RelationalModel->status == 2) {
// php code
}
In above example dataModel is called twice, so same query will be executed in same code twice ( if object is not cached) , in that case query caching can be effective. The code is just a basic idea, its not a real code or working code of any kind.
So what we do?
1. If we have more read and less writes to the tables then query cache can be efficient. Sites like forums, blogs, directory, news sites e.t.c has very less update frequency so query cache will be effective.
2. Limiting query cache size can be good option like few mega byte or few hundred mega byte relative to huge data size will limit the frequency of query caching.
3. if possible, disable query cache and test over all effect on performance can be good idea.
MySQL optimization depends on the database usage frequency and database data size, so testing different scenario with benchmark will be the best option. There is no hard written rule on optimization. But every time we optimize MySQL server just enabling the query cache is not always the best option.
How To Tutorials 