… – 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…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… their master and slave and loaded them into my schema as separate tables. Then I simply created a view of a UNION DISTINCT… TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave… the ‘all_known_index_usage’ and see the union of both of those datasets. This, of course, can be extended to all the datasets…
Comment: Shard-Query adds parallelism to queries
November 15, 2010 By 18 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 14 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 23 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: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… this will reduce overhead associated with grabbing and releasing innodb_thread_concurrency slot but will … innodb tables. optimizer_switch is the catch all variable for a lot of optimizer options. …VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,…
