… 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… 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
… UNION simply: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-01′ AND ’1990-01-02′ -> UNION ALL… all the results from the UNION queries, making the latter more performant than the former. Of course you can stil break all this UNION…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use…handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name… UNION to avoid filesort of full table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… that it works with all MySQL storage engines. This set of benchmarks evaluates how well Infobright community edition (ICE) performs in combination with…: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism…
Comment: UNION vs UNION ALL Performance
December 5, 2007 By 13 Comments
[...] Warum? UNION vs UNION ALL Performance | MySQL Performance Blog __________________ Siste gradum teque aspectu ne subtrahe nostro! Quem fugis? Extremum fato, quod [...]
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… to perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to …54268 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… view over a union of all the already joined and aggregated data from all the nodes. A … which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …performance level. Soon you will be able to set up computation over ICE, InnoDB, Vectorwise and other databases, all…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL… subselects, inline views, parts of the UNION. This is also common missunderstanding. Works on… in invalidation – If table gets modification all queries derived from this table are …
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 [...]
