…. 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…
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… table. – since i use ORDER BY + LIMIT, I can then compute the max() or min() (depending on the order) or the relevant row…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R DESC; +———————-+———-+——-+——-+————–+ | STATE | Total_R | Pct_R | Calls…response time. If there were, I could add in a UNION to inject another row for “lost time” and show …
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_keys… 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…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…, a.constraint_type, convert(group_concat(DISTINCT b.column_name ORDER BY b.ordinal_position SEPARATOR ‘, ‘), char) as column_list, b.referenced… BY a.table_schema, a.table_name, a.constraint_name, a.constraint_type, b.referenced_table_name, b.referenced_column_name UNION…_schema=’test’ GROUP BY table_schema, table_name, constraint_name, constraint_type, referenced_table_name, referenced_column_name ORDER BY table_schema, table…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 19, 2007 By 10 Comments
Peter, Sorry I made a mistake. Finally the query (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 10,takes only 0.03
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If… is not optimally indexed – this is easy. For example our ORDER BY rank query is such – adding index on rank column maxes…’ve seen people trying to join all queries in single UNION (with padding to accommodate different types and number of columns…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
February 6, 2009 By 14 Comments
… above. However, is it also good to use such a union operation for about 1000 Queries to query for about 20…. I mean, about 1000 times. (SELECT …….. LIMIT 15) UNION ALL (SELECT ……… LIMIT 15) . . ORDER BY CREATED LIMIT 15 Or do you advice me…
