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 ?
Best Answer
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.