…54268 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL… ? mysql> explain (select * from utest where c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by ord …
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
… 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: 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 my… 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… to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where j…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… (even though a bit ugly) will be UNION workaround I already wrote about. So what …id=345 order by last_online desc limit 10; There could be many possible limiting factors with all …in ORDER BY in other table. Here is example when ORDER BY is done by second table which requires filesort: mysql> …
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 are …WHERE Year BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…materialized view over a union of all the already joined and aggregated data from all the nodes. A …resource 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
… you with all the data points you need to attack the right query in the right way. But vanilla MySQL does have… 2.5% 51 0.0039 1.00 0.00 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
…”lower” characters ! Yes I often use this too, in MySQL and in Postgres, it is just one of … coordinates, date, item type, etc, and the all-time favourite, union from a huge archive table and an … that rows which have the ORDER BY field greater (or lower depending on the order) to the value computed …
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
…I often use this too, in MySQL and in Postgres, it is just one of the … optimizer should be able to move ORDER BY and LIMIT around inside the execution plan by itself but since it isn’… GPS coordinates, date, item type, etc, and the all-time favourite, union from a huge archive table and an active …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will… queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT…’ve seen people trying to join all queries in single UNION (with padding to accommodate different…
