#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    266
    Rep Power
    10

    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:

    Code:
        (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!?

    So how do UNION's get cached then?

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,347
    Rep Power
    4281
    try like this --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    266
    Rep Power
    10
    But sub-selects aren't cached either!?

IMN logo majestic logo threadwatch logo seochat tools logo