MySQL cache is not getting hit by subqueries

Question

I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior.

Let's say I have an item table

 ID | Item
-----------
 1  | Item_1
 2  | Item_2
 3  | Item_3

The query that will be used here is : SELECT id FROM items

The first time I use it I have a +1 in my status Qcache_inserts, the second time I use it I have +1 to Qcache_hits. Perfect it's working fine.

Now if I use this query : SELECT COUNT(1) FROM (SELECT id FROM items) my_table

The subquery SELECT id FROM items is supposed to already be present in the cache, but I can't get any hit.

Aren't subquery simple thread that executes first ? Then why it is not hitting my query cache ?

Answer 1

MySQL current 5.1 and 5.5 versions do not cache subqueries. Only whole queries. Subqueries are not processed as a separate item and the execution planned created is for the whole query.

MariaDB (a MySQL fork), version 5.3 has an optimization feature that does exactly that: Subquery cache.

If I am not wrong a similar feature will be incorporated in MySQL 5.6.

Answer 2

Your example is too simplified. Let's investigate your real question. Please present SHOW CREATE TABLE and EXPLAIN.

In general, I say "turn off the QC". This is especially important if data is constant flowing into your main tables. Note that all queries for a table (in the QC) are flushed every time a write occurs to the table.