Caching data using MySQL

I want to cache data on MySQL

SET GLOBAL query_cache_siz开发者_如何学编程e = SOME_SIZE;

Is it all the thing required for caching data [efficiently] in MySQL ?

Do I need to add something extra to use the cache efficiently ?

I don't have good knowledge on data caching but still need to use for performance issue, so if I've missed to give some vital info, answer this question assuming the system is in default state.

I don't usually recommend using the MySQL query cache. It sounds great in theory, but unfortunately isn't a great win for caching efficiently, because access to it from queries is governed by a mutex. That means many concurrent queries queue up to get access to the query cache, and this harms more than it helps if you have a lot of concurrent clients.

It even harms INSERT/UPDATE/DELETE, even though these queries don't have result sets, because they purge query results from the query cache if they update the same table(s). And this purging is subject to the same queueing on the mutex.

A better strategy is to use memcached for scalable caching of specific query results, but this requires you to think about what you want to cache and to write application code to access memcached and fail back to MySQL if the data isn't present in the cache. That's more work, but if you do it right it gives better results.

See TANSTAAFL.

There are quite a few settings used for caching different things within MySQL. This is a good guide to optimizing MySQL:

http://www.fromdual.com/mysql-performance-tuning-key

Be careful, the query cache is very specific in what it does:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.
http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

Therefore, if anything in the related tables change, or the query is even reworded, the cache isn't used. So select * from T where id in (1,2) and select * from T where id in (2,1) are different.

SHOW VARIABLES LIKE '%query_cache%';

Will show you the current settings for the cache. But its not as simple as just turning it on, the queries you run need to have result sets that are cacheable and it would take more than this comments box to explain that.

If you have a particular query that you think should be cached then post it and we may be able to determine if it is cacheable.