… – because UNION ALL does not need to use temporary table to store result set, however this is not correct – both UNION ALL and UNION distinct use… for Optimizer Team to look into. Interesting enough the fact UNION and UNION ALL require temporary table can only be seen in SHOW STATUS… UNION and UNION ALL (which is too bad as execution for them is obviously different). The difference in execution speed comes from the fact UNION…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
…-01-01′ AND ’1990-01-02′ -> UNION ALL -> SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * INTO…-01-01′ AND ’1990-01-02′ -> UNION ALL -> SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * INTO…
Comment: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
…’,'pony’) will work as well. I have yet to support UNION and UNION ALL statements which I will be adding support for at the… that capability You could rewrite the OR clause to use UNION. It will work fine without the partitioning as long as…
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… not smart enough in this case to “dive into” the union and add ORDER BY ORD LIMIT 10 to individual queries. What…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…name FROM people WHERE age BETWEEN 18 and 20 AND zip IN (12345,12346, 12347); +———————————-+ | name | +———————————-+ | ed4481336eb9adca222fd404fa15658e | | …10) UNION ALL (select * from people where age=19 order by last_online desc limit 10) UNION ALL (…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… age=18 AND zip IN (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…
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…) JOIN dim_flight using (flight_id) WHERE Year BETWEEN 2000 and 2003 AND Carrier = ‘AA’ GROUP BY Year, Carrier,dest.CityName ORDER…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…value across all tables, …UNION=(`a1`,`a2`) | +——-+————————————————————————————————————————————————————————+ As you can see Merge table does not even stores auto_increment value (and…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… row pointers from all indexes, intersect them and when lookup the data. Which method works better depends on selectivity and correlation. If where… combined index is useless and MySQL has an option of doing full table scan or doing the Union (instead of intersection) on… though it is in memory workload. For UNION case the optimizer is more advanced and it is able to deal with ranges…
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.
