Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Whilst it is called a subquery, it isn't processed (and therefore cached) as a separate query. –  Phil Apr 5 '12 at 10:08

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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