Description:
Hello,
We have suffered from a case, which is not optimized well so that an unnecessary tmp table is generated, leading to bad performance. In this case, we select from a subquery with equality where clause of primary key, and in the subquery there are union all clauses. For instance:
select id from (select id from sbtest1 union all select *id from sbtest2) where id = 100;
In the previous sql statement, `id` is primary key of table `sbtest1` and `sbtest2`, so I think it's equivalent if it's optimized as :
select id from sbtest1 where id = 10 union all select id from sbtest2 where id = 100;
But the 2 statements show different performance with giant gap:
mysql> select id from sbtest1 where id = 100 union all select id from sbtest2 where id = 100;
+-----+
| id |
+-----+
| 100 |
| 100 |
+-----+
2 rows in set (0.00 sec)
mysql>
mysql> select id from (select id from sbtest1 union all select id from sbtest2) where id = 100;
+-----+
| id |
+-----+
| 100 |
| 100 |
+-----+
2 rows in set (58.18 sec)
Also there is a similar case:
We firstly create a view:
create view v_2 as select * from sbtest1 union all select * from sbtest2;
And query from the view with where clause of primary key also shows bad performance:
mysql> create view v_2 as select * from sbtest1 union all select * from sbtest2;
Query OK, 0 rows affected (0.01 sec)
mysql> select id from v_2 where id = 100;
+-----+
| id |
+-----+
| 100 |
| 100 |
+-----+
2 rows in set (57.32 sec)
How to repeat:
# We first construct 2 tables with 10,000,000 records throught sysbench;
mysql>
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL,
`k` int NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql> show create table sbtest2\G
*************************** 1. row ***************************
Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
`id` int NOT NULL,
`k` int NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.33 sec)
mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.31 sec)
# Execute the following statements
mysql>
mysql> select id from sbtest1 where id = 100 union all select id from sbtest2 where id = 100;
+-----+
| id |
+-----+
| 100 |
| 100 |
+-----+
2 rows in set (0.00 sec)
mysql> select id from (select id from sbtest1 union all select id from sbtest2) where id = 100;
+-----+
| id |
+-----+
| 100 |
| 100 |
+-----+
2 rows in set (59.74 sec)
mysql> drop view if exists v_2;
Query OK, 0 rows affected (0.00 sec)
mysql> create view v_2 as select * from sbtest1 union all select * from sbtest2;
Query OK, 0 rows affected (0.00 sec)
mysql> select id from v_2 where id = 100;
+-----+
| id |
+-----+
| 100 |
| 100 |
+-----+
2 rows in set (56.65 sec)
mysql>
Description: Hello, We have suffered from a case, which is not optimized well so that an unnecessary tmp table is generated, leading to bad performance. In this case, we select from a subquery with equality where clause of primary key, and in the subquery there are union all clauses. For instance: select id from (select id from sbtest1 union all select *id from sbtest2) where id = 100; In the previous sql statement, `id` is primary key of table `sbtest1` and `sbtest2`, so I think it's equivalent if it's optimized as : select id from sbtest1 where id = 10 union all select id from sbtest2 where id = 100; But the 2 statements show different performance with giant gap: mysql> select id from sbtest1 where id = 100 union all select id from sbtest2 where id = 100; +-----+ | id | +-----+ | 100 | | 100 | +-----+ 2 rows in set (0.00 sec) mysql> mysql> select id from (select id from sbtest1 union all select id from sbtest2) where id = 100; +-----+ | id | +-----+ | 100 | | 100 | +-----+ 2 rows in set (58.18 sec) Also there is a similar case: We firstly create a view: create view v_2 as select * from sbtest1 union all select * from sbtest2; And query from the view with where clause of primary key also shows bad performance: mysql> create view v_2 as select * from sbtest1 union all select * from sbtest2; Query OK, 0 rows affected (0.01 sec) mysql> select id from v_2 where id = 100; +-----+ | id | +-----+ | 100 | | 100 | +-----+ 2 rows in set (57.32 sec) How to repeat: # We first construct 2 tables with 10,000,000 records throught sysbench; mysql> mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int NOT NULL, `k` int NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) mysql> show create table sbtest2\G *************************** 1. row *************************** Table: sbtest2 Create Table: CREATE TABLE `sbtest2` ( `id` int NOT NULL, `k` int NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.33 sec) mysql> select count(*) from sbtest2; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.31 sec) # Execute the following statements mysql> mysql> select id from sbtest1 where id = 100 union all select id from sbtest2 where id = 100; +-----+ | id | +-----+ | 100 | | 100 | +-----+ 2 rows in set (0.00 sec) mysql> select id from (select id from sbtest1 union all select id from sbtest2) where id = 100; +-----+ | id | +-----+ | 100 | | 100 | +-----+ 2 rows in set (59.74 sec) mysql> drop view if exists v_2; Query OK, 0 rows affected (0.00 sec) mysql> create view v_2 as select * from sbtest1 union all select * from sbtest2; Query OK, 0 rows affected (0.00 sec) mysql> select id from v_2 where id = 100; +-----+ | id | +-----+ | 100 | | 100 | +-----+ 2 rows in set (56.65 sec) mysql>