… smart enough in this case to “dive into” the union and add ORDER BY ORD LIMIT 10 to individual queries. What if… c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by ord desc limit 10) order by ord… unable to handle even basic UNION with limit (without order by) optimally – in creates result set for the union fully and when only…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…=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…
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… 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: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in… back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 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…) WHERE Year BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…and traversing ranges between values in sorted order. For example when you query AGE=18…i1,i2 | 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ 1 row in …
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…
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…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… result set is treated as a materialized view over a union of all the already joined and aggregated data from all…. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set processing is…_id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count…
Comment: What does Using filesort mean in MySQL?
October 18, 2011 By 27 Comments
… but interesting example: EXPLAIN SELECT 20 AS C UNION SELECT 10 AS C ORDER BY C id select_type table type possible… NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL Using filesort Obviously…
