… from tab_a union all select distinct b col from tab_b union all select distinct c col from tab_c union all select distinct d col from tab_d ) 2)select distinct(col) from(select distinct a col from tab_a union all select b col from tab_b union all select c col from tab_c union all select d col from…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
… LIMIT inside of union clause being cool thing. But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION DISTINCT) ? Indeed… than UNION as it indeed should be. So why UNION ALL is faster than UNION DISTINCT ? The first informed guess would be – because UNION ALL does…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… ’1990-01-02′ -> UNION ALL -> SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * INTO OUTFILE… ’1990-01-02′ -> UNION ALL -> SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * INTO OUTFILE…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…_online desc limit 10) UNION ALL (select * from people where age=19 order by last_online desc limit 10) UNION ALL (select * from people… | 12741 | Using where | | 3 | UNION | people | ref | age | age | 1 | const | 12631 | Using where | |NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=21 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…: 54268 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL ref…: 54268 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL ref…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… `StateFips`, OriginStateName as `StateName` , OriginWac as `Wac` FROM ontime_stage UNION select Dest as `airport_code`, DestCityName as `CityName`, DestState as…: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism…
Comment: UNION vs UNION ALL Performance
November 8, 2007 By 13 Comments
Union All is generally faster than union because it doesn’t have to do a sort / unique step. With union all, you smoosh two data sets together not caring if there are dups or not. union (distinct) has to then go that extra step to remove dups. TANSTAAFL
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Peter, Sorry, but you made a very small mistake …. It should be UNION ALL and not just UNION . As you know, UNION without attribute is UNION DISTINCT.
Comment: Shard-Query adds parallelism to queries
November 18, 2010 By 16 Comments
I’ve checked support for UNION, UNION ALL, and subqueries in the FROM clause into SVN. If you … usual way to rewrite such a query is to use UNION, and so I think it suffices here.
