The MySQL query cache can be very useful in environments where data is not modified often, and traffic consists of mostly reads. It can improve performance by quite a bit if used correctly, but can actually degrade performance if configuration, queries, and traffic patterns are not optimized for it.

Let me quickly go over what the query cache is, and what it is not. The query cache does not cache the query execution plan, the full page on disk (which is what the InnoDB buffer pool is used for), DDL statements, or any queries that modify data (INSERT/UPDATE/etc). The query cache *does* cache the full result set of “cacheable” SELECT queries. For a query to be “cacheable”, it must have the following properties:

• It must be deterministic. The query must return the same result set each time that it is run. This means that it may not contain any non-deterministic variables, such as USER(), NOW(), LAST_INSERT_ID(), etc.
• It must begin with “SELECT”. The query cache filters queries by examining the first character. If there is any whitespace or comments preceding the query, MySQL will not recognize it as cachable and will skip it.
• It must not begin with “SELECT SQL_NO_CACHE”. This syntax will cause the query to bypass the cache altogether.
• The query must look exactly the same each time it is run. Any query that does not match another in the cache will be treated as a separate query. Ensure that the query has the same letter casing, comments, and/or variables on each execution to avoid this.
• It must be a full query. Subqueries and parts of a UNION are not supported. 
• Queries in the cache are purged each time a table used by one of them is modified. 

On top of query optimization, there are also a few variables you need to configure to maximize performance of the query cache.

• query_cache_size - The most important one. This is the size of the query cache (in bytes). Setting it to 0 disables query caching.
• query_cache_limit - The maximum size of a query that will be cached (in bytes).
• query_cache_type - 0 (disabled), 1 (enabled), 2 (enabled for cacheable queries that begin with “SELECT SQL_CACHE”

Once the query cache is configured, there are a few status variables that you can monitor to help optimize further.

• Qcache_free_blocks - Number of free blocks in the query cache.
• Qcache_free_memory - Amount (in bytes) of free memory in the query cache.
• Qcache_hits - Number of successful hits to the query cache.
• Qcache_inserts - Number of queries added to the query cache.
• Qcache_lowmem_prunes - Number of queries removed from the query cache due to low memory.
• Qcache_not_cached - Number of noncached queries.
• Qcache_queries_in_cache - Total number of queries contained in the query cache.
• Qcache_total_blocks - Total number of blocks allocated to the query cache.

Keep in mind that the query cache is not suitable for all environments. If your traffic pattern consists of a high write to read ratio, the performance overhead of maintaining the query cache (which inspects each select statement before execution) may actually hurt performance more than it improves it. Be sure to keep a close eye on the status variables and determine what configuration will work best in your environment.