… manually ? mysql> explain (select * from utest where c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by… the fact MySQL is unable to handle even basic UNION with limit (without order by) optimally – in creates result set for the union fully and when only takes 10 rows from it: mysql…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but…most time, because it’s sorted in execution order, not order of time consumption. Here is one query… If there were, I could add in a UNION to inject another row for “lost time” …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… use UNION to avoid filesort of full 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 age=20 order…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | …clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features…1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
… performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in… order by and limit using index merge is faster than UNION as it indeed should be. So why UNION ALL is faster than UNION… to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have… you have ORDER BY col1 DESC, col2 DESC same thing, however if you would have ORDER BY col1, col2 DESC MySQL will have…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed …resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law … dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we …2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_… ‘post_tag’, ‘post_format’) AND tr.object_id IN (733) ORDER BY t.name ASC\G Let’s again take a …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… between values in sorted order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the… combined index is useless and MySQL has an option of doing full table scan or doing the Union (instead of intersection) on… memory workload. For UNION case the optimizer is more advanced and it is able to deal with ranges: mysql [localhost] {msandbox} (test…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… rows, with mysql 5.0.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…
