To UNION or not to UNION...

Recently a forum question
[ 1
] got my attention:

*Is there any performance issue with Union?

I used union all sometime back and it was performance issue just to make an opinion that we should used union in query.*

The question itself was not too interesting because the answer is easy: It depends. But I wanted to see if there was an improvement in this common problem over time in MySQL.

Test set-up

So I prepared a little test to simulate some of the possible scenarios:

CREATE TABLE `u` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=InnoDB
;

INSERT INTO u SELECT NULL, ROUND(RAND()*10, 0), ROUND(RAND()*10, 0), ROUND(RAND()*1000000, 0), ROUND(RAND()*1000000, 0);
INSERT INTO u SELECT NULL, ROUND(RAND()*10, 0), ROUND(RAND()*10, 0), ROUND(RAND()*1000000, 0), ROUND(RAND()*1000000, 0) FROM u;
... 1 mio rows

ANALYZE TABLE u;

With this table we can simulate the OR problem with low and high selectivity.

Running the tests

We did the tests with MySQL (5.0 - 5.7), Percona Server (5.6) and MariaDB (5.5, 10.0) for the following queries:

EXPLAIN SELECT * FROM u WHERE a = 5 OR b = 5;
EXPLAIN SELECT * FROM u WHERE a = 5 OR c = 500001;
EXPLAIN SELECT * FROM u WHERE c = 500001 OR d = 500001;

We are interested in what the optimizer is doing and what the performance of the queries is. The following results came out:

Different Query Execution Plans (QEP)

QEP 1:

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | u     | ALL  | a,b           | NULL | NULL    | NULL | 1049134 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

QEP 2:

+----+-------------+-------+-------------+---------------+------+---------+------+--------+-------------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows   | Extra                         |
+----+-------------+-------+-------------+---------------+------+---------+------+--------+-------------------------------+
|  1 | SIMPLE      | u     | index_merge | a,c           | a,c  | 5,5     | NULL | nnnnnn | Using union(a,c); Using where | 
+----+-------------+-------+-------------+---------------+------+---------+------+--------+-------------------------------+

QEP 3:

+----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                         |
+----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
|  1 | SIMPLE      | u     | index_merge | c,d           | c,d  | 5,5     | NULL |    n | Using union(c,d); Using where | 
+----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+

Conclusion

• Single query performance went down from 5 - 50% (in one case increased by 5%) over time (MySQL releases). But we can see some impacts on optimizer improvements.
• Newer MySQL releases are not necessarily faster for single-query performance than older ones. Most of the MySQL users are not running more than 1 or 2 concurrent queries. For them scalability improvements are not really an issue.
• There seems to be some changes in the Optimizer some for good, some for bad, depending on the release or branch/fork you are using. So test carefully when you change the release or branch/fork.
• And: Do not believe the whole marketing yelling but do your own testing…