Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I was reading about the mysql query cache and I read this..

Caching full queries only – Meaning it does not work for subselects, inline views, parts of the UNION. This is also common missunderstanding.

What does it mean by "parts of the UNION"? I'm working on a website that makes use of a lot of UNION's and I think they are being cached, but I want to make sure.

Thanks!

share|improve this question

1 Answer 1

up vote 3 down vote accepted

UNION combines subqueries, and these subqueries themselves are not considered by the query cache mechanism, only the full query and its end result are.

As an example, imagine that this query would be run by an application on a frequent basis:

select 'nb1', count(*) from table1 group by col1
UNION
select 'nb2', count(*) from table2 group by col2
UNION
select 'nb3', count(*) from table3 group BY col3

When re-running this and no change has occurred in table1,table2,table3, the query cache can skip the execution and re-send the cached result.

"It does not work for parts of the UNION" means that if another step in the application runs this query:

 select 'nb1', count(*) from table1 group by col1

then even though the result is somehow already cached as a part of the result of the UNION query, the query cache is unable to recognize that to take advantage of it. It will execute it and put its result in the cache as a completely independent query.

share|improve this answer
    
Ok, thanks. Makes sense now that you explain it like that. :) –  Brett Aug 21 '12 at 9:58
    
The union query example above probably won't cache - it has a leading space - see also (e.g.) mysqlperformanceblog.com/2006/07/27/mysql-query-cache –  symcbean Aug 21 '12 at 12:26
    
Good catch. Leading space removed, although I wouldn't expect people to copy-paste this sample query! –  Daniel Vérité Aug 21 '12 at 13:09

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.