… smart enough in this case to “dive into” the union and add ORDER BY ORD LIMIT 10 to individual queries. What if we… where c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by ord desc limit 10) order by ord desc limit… unable to handle even basic UNION with limit (without order by) optimally – in creates result set for the union fully and when only takes…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in interactive applications… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have… example above we did order by by last column, in fact index can be used for ORDER BY if sorting is done by leading column(s…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… 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 age=20 order by last_online desc limit 10) ORDER BY last…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… collation specific. Q: ORDER By optimization issues: select * from table where A=xxx and B between 100 and 200 order by B Very common… order by on another column. do i need to add the index on column using order by clause. A: If index is used for ORDER BY… complicated conditions you will need to use something like Trick “Unionizing Order by” described in presentation. Q: what is the impact on indexing…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… 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… with order by and limit using index merge is faster than UNION as it indeed should be. So why UNION ALL is faster than UNION DISTINCT ? The first informed guess would be – because UNION…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then…) WHERE DepDelay>10 AND Year BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC; — Q3 SELECT CityName as Origin, count(*) AS… (date_id) WHERE Year BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index_stats) ORDER BY TABLE_SCHEMA, TABLE_NAME; Now I can query the ‘all_known_index_usage’ and see the union of both…_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES from information…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… = X OR indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, as we will… user_id > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM… user_id > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1\G *************************** 1. row *************************** id: 1 select_type…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
….45 (SELECT * FROM utest WHERE c1=5) union (SELECT * FROM utest WHERE c2=5) ORDER BY ord DESC LIMIT 10; 10 rows in set (0.31 sec) (SELECT * FROM utest WHERE c1=5 ORDER BY ord DESC LIMIT 10) union (SELECT * FROM utest WHERE c2=5 ORDER BY ord DESC LIMIT 10) ORDER BY ord DESC LIMIT…
Comment: When the subselect runs faster
April 17, 2010 By 9 Comments
… id from table where col1=’a’ union select id from table where col1=’b’ )ORDER BY id DESC LIMIT 20 or even: select t1.* from table t1, ( select id from table where col1=’a’ union select id from table where col1=’b’ ) t2 where t1.id = t2.id ORDER BY id DESC LIMIT 20
