….0 solution – using UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union (select * from utest… for the union fully and when only takes 10 rows from it: mysql> explain (select * from utest where c1=5 ) union (select * from utest… test data. Another possible optimizer improvement to do. P.S This post is inspired by Does MySQL Optimize UNION with LIMIT clause topic…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… having optimization of moving LIMIT inside of union clause being cool thing. But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is…, having about 40.000.000 of rows select * from test.abc where i=5 union select * from test.abc where j=5 This original query was taking about 22 seconds. As I modified it: select * from…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… different types of ranges in MySQL. IN range allows to optimize lookups on the second key part, while BETWEEN and other… table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select * from people where age=19 order by last_online desc limit 10) UNION ALL (select * from people where…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE… (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…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use the index merge union algorithm, which…_colA and indexed_colB and then do a set-theoretic union of the two result sets. Replace that “OR” with an…, altering the table structure, or creating messy sub-selects. In cases where the optimizer just can’t get things right, and you…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… which it makes sense is collation specific. Q: ORDER By optimization issues: select * from table where A=xxx and B between 100… trick mentioned in the presentation to convert sort to the union for small ranges. Q: In the case of a junction… (A,B) for sorting optimization, for more complicated conditions you will need to use something like Trick “Unionizing Order by” described in…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have… so ORDER BY can be optimized even if it is done by second table: mysql> explain select test.i from test, test… if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity or just better estimates instead…
Comment: Using UNION to implement loose index scan in MySQL
January 21, 2010 By 15 Comments
…, 3) optimized – query returns immediately: select min(m) from ( select min(date_time) as m from tabData where channel_id = 1 union select min(date_time) from tabData where channel_id = 2 union select…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… amount of rows are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return only… much more rows because it is optimized differently. One of typical examples here would be SELECT * FROM tbl WHERE id=5 executed…’ve seen people trying to join all queries in single UNION (with padding to accommodate different types and number of columns…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… very wrong here. This happens because Optimizer assumes columns i1 and i2 are independent estimating selectivity for the intersection. This is as…. For UNION case the optimizer is more advanced and it is able to deal with ranges: mysql [localhost] {msandbox} (test) > explain select avg…
