I have a simple join query, but for some reason query_cache won't cache it!

SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id  WHERE approve =1 AND allow_main =1 ORDER BY FIXED DESC , DATE DESC  LIMIT 7 , 7;

without the JOIN post_plus ON ( post.id = post_plus.news_id ) it's working.

query_cache is on

| query_cache_limit              | 10485760             |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 536870912            |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | ON                   |
| query_cache_wlock_invalidate   | OFF                  |

Server version: 5.6.12-56 Percona Server (GPL), Release rc60.4, Revision 393

both tables are innodb with utf8 charset


I'm starting with the assumption that you believe one query gets cached and not the other based on response time the second time you issue the query. If you're determining that the query isn't cached based on something else, please comment to that effect, explaining your precise observations and what you're expecting instead.

There are several reasons that come to mind why a query would not be cached (or would appear not to be cached). Here are some thoughts. Forgive me if I have stated the obvious:

If the 2nd table is particularly busy, anything that gets cached will be cached for such a short time that you'll never see it. As I mentioned in my response to "Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes" (where I went in to some detail about my finding while investigating the internals of the query cache on stock MySQL) --

Any time the data in a table referenced by a cached query changes, all of the queries that involved that table are removed from the cache -- even if the change doesn't impact the cached results. This is even true if a table changes, but doesn't change, as in the case of an InnoDB transaction that is rolled back. The query cache entries referencing that table were already purged.

`query_cache_limit` = 10485760  

This setting would prevent a resultset larger than 10MB from being cached. Adding the 2nd table will obviously increase the size of the resultset but without more information, it isn't apparent how much.

If the 2nd table is partitioned, the query will not be cached because the query cache does not work with partitioned tables (at least in MySQL 5.6; unsure about Percona Server).

The query cache operates on the query string submitted by the client before it reaches the parser -- for a subsequent query to be served from the cache, it must be byte-by-byte identical to the prior query that was cached. If you don't submit precisely the same query each time, you won't get a cached result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.