Description:
Optimizer can choose index-loose scan strategy for GROUP MIN/MAX or DISTINCT Query to accelerate query, while it need satisfy many rules, one of them is:
```
WA1. There are no other attributes in the WHERE clause except the ones
referenced in predicates RNG, PA, PC, EQ defined above. Therefore
WA is subset of (GA union NGA union C) for GA,NGA,C that pass the
above tests. By transitivity then it also follows that each WA_i
participates in the index I (if this was already tested for GA, NGA
and C).
```
It means where predicate attributes must be a Index attributes subset. this rule is reasonable for non-partition table, but is unreasonable for partition table sometime. when where predicate attribute is the partition key attribute,and the predicate is using for partition prune. after partitions has been pruned in Execute plan, the predicate is no need retained anymore to do filter, because all records obtained from the left partitions can pass then predicate check. and the predicate affect optimizer don't choose index-loose scan strategy.
for example:
```
CREATE TABLE `t_task_simulated_bidding9` (
`id` bigint unsigned NOT NULL COMMENT '',
`taskId` bigint NOT NULL COMMENT '',
`bidPrice` decimal(3,1) NOT NULL COMMENT '',
`acquiredQuantity` int NOT NULL COMMENT '',
`createTime` datetime NOT NULL COMMENT '',
`groupId` varchar(64) DEFAULT NULL COMMENT '',
`searchkey` varchar(255) DEFAULT NULL COMMENT '',
`day` date NOT NULL COMMENT '',
`generateDesc` varchar(256) DEFAULT NULL COMMENT '',
PRIMARY KEY (`id`,`day`),
KEY `t_task_simulated_bidding_taskid_idx` (`taskId`,`groupId`,`searchkey`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT=''
/*!50500 PARTITION BY RANGE COLUMNS(`day`)
(PARTITION p20220107 VALUES LESS THAN ('20220108') ENGINE = InnoDB,
PARTITION p20220108 VALUES LESS THAN ('20220109') ENGINE = InnoDB,
PARTITION p20220109 VALUES LESS THAN ('20220110') ENGINE = InnoDB) */;
```
using where predicate don't choose index loose scan.
```
mysql> explain select distinct(taskid) from t_task_simulated_bidding9 where day = '2022-01-07';
+----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t_task_simulated_bidding9 | p20220107 | index | t_task_simulated_bidding_taskid_idx | t_task_simulated_bidding_taskid_idx | 971 | NULL | 9085 | 10.00 | Using where; Using index |
+----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+
```
no using where predicate and point out partition choose index loose scan.
```
explain select distinct(taskid) from t_task_simulated_bidding9 partition(p20220107);
+----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t_task_simulated_bidding9 | p20220107 | range | t_task_simulated_bidding_taskid_idx | t_task_simulated_bidding_taskid_idx | 8 | NULL | 99 | 100.00 | Using index for group-by |
+----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+
```
How to repeat:
Create table and Query like above.
Description: Optimizer can choose index-loose scan strategy for GROUP MIN/MAX or DISTINCT Query to accelerate query, while it need satisfy many rules, one of them is: ``` WA1. There are no other attributes in the WHERE clause except the ones referenced in predicates RNG, PA, PC, EQ defined above. Therefore WA is subset of (GA union NGA union C) for GA,NGA,C that pass the above tests. By transitivity then it also follows that each WA_i participates in the index I (if this was already tested for GA, NGA and C). ``` It means where predicate attributes must be a Index attributes subset. this rule is reasonable for non-partition table, but is unreasonable for partition table sometime. when where predicate attribute is the partition key attribute,and the predicate is using for partition prune. after partitions has been pruned in Execute plan, the predicate is no need retained anymore to do filter, because all records obtained from the left partitions can pass then predicate check. and the predicate affect optimizer don't choose index-loose scan strategy. for example: ``` CREATE TABLE `t_task_simulated_bidding9` ( `id` bigint unsigned NOT NULL COMMENT '', `taskId` bigint NOT NULL COMMENT '', `bidPrice` decimal(3,1) NOT NULL COMMENT '', `acquiredQuantity` int NOT NULL COMMENT '', `createTime` datetime NOT NULL COMMENT '', `groupId` varchar(64) DEFAULT NULL COMMENT '', `searchkey` varchar(255) DEFAULT NULL COMMENT '', `day` date NOT NULL COMMENT '', `generateDesc` varchar(256) DEFAULT NULL COMMENT '', PRIMARY KEY (`id`,`day`), KEY `t_task_simulated_bidding_taskid_idx` (`taskId`,`groupId`,`searchkey`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='' /*!50500 PARTITION BY RANGE COLUMNS(`day`) (PARTITION p20220107 VALUES LESS THAN ('20220108') ENGINE = InnoDB, PARTITION p20220108 VALUES LESS THAN ('20220109') ENGINE = InnoDB, PARTITION p20220109 VALUES LESS THAN ('20220110') ENGINE = InnoDB) */; ``` using where predicate don't choose index loose scan. ``` mysql> explain select distinct(taskid) from t_task_simulated_bidding9 where day = '2022-01-07'; +----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_task_simulated_bidding9 | p20220107 | index | t_task_simulated_bidding_taskid_idx | t_task_simulated_bidding_taskid_idx | 971 | NULL | 9085 | 10.00 | Using where; Using index | +----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ ``` no using where predicate and point out partition choose index loose scan. ``` explain select distinct(taskid) from t_task_simulated_bidding9 partition(p20220107); +----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_task_simulated_bidding9 | p20220107 | range | t_task_simulated_bidding_taskid_idx | t_task_simulated_bidding_taskid_idx | 8 | NULL | 99 | 100.00 | Using index for group-by | +----+-------------+---------------------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ ``` How to repeat: Create table and Query like above.