Description:
Hello, dear verification team.
We find that the tables in a subquery is not optimized well with `prune_partitions` that all partitiones are finally accessed, which is actualy unnecessary. If there is too many partitions for this table, it will introduce a lot of unnecessary cost.
How to repeat:
[yxx_git1@localhost build]$ mysql -uroot -p'XXXXX'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.25-debug Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database cca;
Query OK, 1 row affected (0.01 sec)
mysql> use cca
Database changed
mysql> create table t1(a varchar(8) not null default '') ;
Query OK, 0 rows affected (0.37 sec)
mysql> create table t2(b varchar(8) not null default '') partition by range columns (b)
-> (PARTITION P23 VALUES LESS THAN ('20210924'),
-> PARTITION P24 VALUES LESS THAN ('20210925'),
-> PARTITION P25 VALUES LESS THAN ('20210926'),
-> PARTITION P26 VALUES LESS THAN ('20210927'),
-> PARTITION P27 VALUES LESS THAN ('20210928'),
-> PARTITION P28 VALUES LESS THAN ('20210929'),
-> PARTITION PMAX VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (2.57 sec)
mysql> insert into t1 values ('20210924');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t2 values ('20210924');
Query OK, 1 row affected (0.01 sec)
# In this case, we access only partition `P24` of table `t2`, but as a result all partitions are accessed
mysql> explain select * from t1 left join (select * from t2 where b = '20210924') dt on a = b;
+----+-------------+-------+------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | P23,P24,P25,P26,P27,P28,PMAX | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `cca`.`t1`.`a` AS `a`,`cca`.`t2`.`b` AS `b` from `cca`.`t1` left join (`cca`.`t2`) on(((`cca`.`t2`.`b` = `cca`.`t1`.`a`) and (`cca`.`t1`.`a` = '20210924'))) where true |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1 left join (select * from t2 where b = '20210924') dt on a = b;
+----------+----------+
| a | b |
+----------+----------+
| 20210924 | 20210924 |
+----------+----------+
1 row in set (0.00 sec)
mysql>
Suggested fix:
consider execute `prune_partitions` optimization for inner tables of subqueries.