… | Using where | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +—-+————–+————–+——-+—————+———–+———+——+——+————-+ 5 rows in set (0.00 sec) Compared to using UNION simply: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-01′ AND ’1990-01-02′ -> UNION…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and… can give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name…
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… UNION ALL does not need to use temporary table to store result set, however this is not correct – both UNION ALL and UNION distinct use… to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…: Using sort_union(c1,c2); Using where; Using filesort 1 row in set (0.00 sec) As you can see MySQL 5.1.21 uses sort… pre MySQL 5.0 solution – using UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union (select… for the union fully and when only takes 10 rows from it: mysql> explain (select * from utest where c1=5 ) union (select * from…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… | 37915 | Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… Any stored code (triggers, stored routines, etc) uses memory to execute; so do temporary tables…. query. Sorting and grouping and DISTINCT and UNION may/will also create temp tables; the… 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) |…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL… 4,4 | NULL | 203803 | Using union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+———————————+ 1…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… RAID array was used as the underlying storage for the virtual machines. Baseline: The MySQL command line client was used to execute the… the following are in used in the query: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism can’t be added…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… in the overview section. So say if you were using the vanilla MySQL server, you would see an entry like this in… 2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp… that you can use to gather more data about the underlying tables involved and the query execution plan used by MySQL. The end…
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 [...]
