UNION and MySQL Query Cache

[djuntgen]

Hi -

In this post: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-c ache/

Peter says that “parts of a UNION” may not be cached.

I don’t understand this, maybe I don’t understand fully how MySQL executes a UNION. So my question is can a UNION be cached and if it can which parts cannot?

Thanks,

–Dave

[vgatto]

I think the article is just explaining that the query cache is a very simple mapping between the text of query and its result set. MySQL doesn’t really understand the query beyond it being a collection of bytes for the purposes of query cache lookup.

So if you execute

SELECT id FROM widgets WHERE state = 0
UNION
SELECT id FROM widgets WHERE state = 1

you should expect the result set for that exact query to be cached, and not the result set for

SELECT id FROM widgets WHERE state = 0

or the result set for

SELECT id FROM widgets WHERE state = 1

unless the queries are independently executed.

You can verify this by watching SHOW STATUS LIKE ‘Qcache%’ on a quiet server.