… 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… UNION ALL require temporary table can only be seen in SHOW STATUS – EXPLAIN does not want to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where j…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… using UNION simply: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-01′ AND ’1990-01-02′ -> UNION ALL… this post. mysql [localhost] {msandbox} (employees) > SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-01′ AND ’1990-01-02′ -> UNION ALL -> SELECT…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE… 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…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL storage engines. This set….02 sec) Each year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | 135125787 | +———–+ 1…: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
….length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is the simple case… NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update… the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…MySQL 5.0 solution – using UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union… Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL…
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… log all the extra stats I mentioned above in the overview section. So say if you were using the vanilla MySQL server… 2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… as a materialized view over a union of all the already joined and aggregated data from all the nodes. A single temporary table…. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set processing is… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
October 5, 2007 By 10 Comments
[...] I was comparing 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 benchmarks, and he was [...]
