… 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 93 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 12 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: UNION vs UNION ALL Performance
October 5, 2007 By 13 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 22 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…
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
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set processing is…_date.date_id) WHERE dim_date.Year IN (2008) GROUP BY 1 ORDER BY NULL [1] => SELECT origin_airport_id AS `origin_airport…_date.date_id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… can see there is a plethora of useful information reported by Percona Server. Another great thing about Percona Server is the… 2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp… 1.2% 50 0.0020 1.00 0.01 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp…’, ‘post_tag’, ‘post_format’) AND tr.object_id IN (733) ORDER BY t.name ASC\G Let’s again take a look at…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
… the optimizer should be able to move ORDER BY and LIMIT around inside the execution plan by itself but since it isn’t… coordinates, date, item type, etc, and the all-time favourite, union from a huge archive table and an active table, plus… I know that rows which have the ORDER BY field greater (or lower depending on the order) to the value computed above will…
