Search code examples
mysqlcachingunionquery-cache

Caching MySQL UNION Queries

I've read that MySQL can cache UNION's, but at the same time I have read that..

Avoid comment (and space) in the start of the query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries are cached – so it looks at first letter of the query and if it is “S” it proceeds with query lookup in cache if not – skips it.

Source: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

So with that said, since UNION's start with a parentheses how are they cached?

As per mysql.com:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

First letter is NOT an "S", so it would be skipped!?

I've also seen you can do something like this..

SELECT * FROM (
    (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
    UNION ALL
    (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
) AS u

But then it would be a sub-select and hence, according to that page, won't be cached either.

So how do UNIONs get cached then?

Solution

  • this is because every SELECT query can be written with 'S' as it's starting letter.

    you don't need to write sub-query to cache this UNION query as brackets can be removed from your query and can be modified as:

    SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10
UNION
SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10;