… (select * from test.abc where i=5) union all (select * from test.abc where j=5 and i!=5) \G *************************** 1. row *************************** id: 1 select…: NULL ref: NULL rows: NULL Extra: 3 rows in set (0.00 sec) In fact EXPLAIN output is the same for UNION and UNION ALL (which…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… UNION ALL -> SELECT * FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * …NULL | 123 | Using where | | 4 | UNION | employees | range | hire_date | hire_date | 3 | NULL | 179 | Using where | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…UNION ALL (select * from people where age=19 order by last_online desc limit 10) UNION ALL (select…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
April 2, 2010 By 57 Comments
…_ROWS */ NULL FROM aaa LIMIT 0 UNION ALL SELECT NULL FROM bbb LIMIT 0 instead of SELECT /*! SQL_CALC_FOUND_ROWS */ * FROM aaa LIMIT 0 UNION ALL SELECT * FROM bbb LIMIT 0; and then SELECT…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…. CREATE TABLE TreePaths ( ancestor CHAR(1) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY (ancestor, descendant… (ancestor, descendant, length) SELECT t.ancestor, ‘D’, t.length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using filesort 3 rows… where *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using filesort 3 rows…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME…_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX_NAME != ‘PRIMARY’ and all_indexes.NON_UNIQUE = 1 order by index…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…,i2 | 4,4 | NULL | 203803 | Using union(i1,i2); …select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+——+—————+——+———+——+———-+————-+ | 1 | SIMPLE | idxtest | ALL | i1,combined | NULL | NULL | NULL…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use the index merge union …UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0,…ways to fix this issue, and they are all straightforward to implement. The bad (sort of) …
