unionする前にwhereをするのと、
unionした後にwhereをする場合、
mysqlにおいて、差異はあるのかを確認してみた。
動機としては、
「unionする前にwhereをする」のと「unionした後にwhereをする」が同パフォーマンスであれば、
multisourceでviewを作っておいても十分高速に引ける可能性がある、と思ったところです。
いろいろなバージョンで試してみました。
予め以下のようなテーブルを作成しておき、100万件登録しておきます。
> desc users; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | email | varchar(128) | NO | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
(MySQL5.6)
mysql> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+ | 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | NULL | | 2 | DERIVED | users | ALL | NULL | NULL | NULL | NULL | 996473 | NULL | | 3 | UNION | users | ALL | NULL | NULL | NULL | NULL | 996473 | NULL | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+ 4 rows in set (0.12 sec) mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ 4 rows in set (0.00 sec)
(MySQL5.7)
mysql> explain select * from (select * from users union all select * from users) as t where id = 7542; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 996473 | 100.00 | NULL | | 3 | UNION | users | NULL | ALL | NULL | NULL | NULL | NULL | 996473 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ 3 rows in set, 1 warning (0.00 sec) mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 3 | UNION | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 3 rows in set, 1 warning (0.00 sec)
(MySQL8)
mysql> explain select * from (select * from users union all select * from users) as t where id = 7542; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 996761 | 100.00 | NULL | | 3 | UNION | users | NULL | ALL | NULL | NULL | NULL | NULL | 996761 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ 3 rows in set, 1 warning (0.05 sec) mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 3 | UNION | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 3 rows in set, 1 warning (0.00 sec)
(MariaDB10.1)
MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1994176 | Using where | | 2 | DERIVED | users | ALL | NULL | NULL | NULL | NULL | 997088 | | | 3 | UNION | users | ALL | NULL | NULL | NULL | NULL | 997088 | | +------+-------------+------------+------+---------------+------+---------+------+---------+-------------+ 3 rows in set (0.01 sec) MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.00 sec)
(MariaDB10.2)
MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.001 sec) MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.001 sec)
(MariaDB10.3)
MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.001 sec) MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.000 sec)
これ、正直言って、動機を満たす結果を得るのは無理だと思ってたが、、、
MariaDB10.2と10.3は嬉しい結果を出してくれました。
以上