3

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 ?

  • Whilst it is called a subquery, it isn't processed (and therefore cached) as a separate query. – Philᵀᴹ Apr 5 '12 at 10:08
4

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.

1

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.

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.