… manually ? mysql> explain (select * from utest where c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by ord desc… 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: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have… to use in ORDER BY in other table. Here is example when ORDER BY is done by second table which requires filesort: mysql> explain select test…, if you have ORDER BY col1 DESC, col2 DESC same thing, however if you would have ORDER BY col1, col2 DESC MySQL will have to…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
… to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * … WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R DESC; +———————-+———-+——-+——-+————–+ | STATE | Total_R …were, I could add in a UNION to inject another row for “lost…
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…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…improve the performance of queries against a MySQL database by distributing the work over multiple … clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features…WHERE Year BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance…
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… 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… to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where…
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 … which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …id) WHERE 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
…2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_…plan used by MySQL. The end result might be that you end up limiting the number of results returned by the query, by … ‘post_tag’, ‘post_format’) AND tr.object_id IN (733) ORDER BY t.name ASC\G Let’s again take a look …
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 DESC LIMIT…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
…”lower” characters ! Yes I often use this too, in MySQL and in Postgres, it is just one of …date, item type, etc, and the all-time favourite, union from a huge archive table and an active table… that rows which have the ORDER BY field greater (or lower depending on the order) to the value computed …
