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