| Bug #116135 | partition prune incorrect | ||
|---|---|---|---|
| Submitted: | 18 Sep 2:38 | Modified: | 19 Sep 4:52 |
| Reporter: | haizhen xue | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[18 Sep 2:38]
haizhen xue
[18 Sep 9:33]
MySQL Verification Team
Hi Mr. xue, Thank you very much for your bug report. We have managed to repeat your test fully: id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 p20240814,p20240901 range idx_createTime idx_createTime 5 NULL 1 100.00 Using index condition However, this is not a bug. Simply, for these types of queries, first partition always has to be consulted for some index statistics. Not a bug.
[19 Sep 4:52]
haizhen xue
mysql> explain select feeid, customerid, relid, businessunit, resourcetag, resourcename, resourceid, substring(convert_tz(createtime,'+00:00','+08:00'),1,19) as createtime, substring(convert_tz(updatetime,'+00:00','+08:00'),1,19) as updatetime, extendparam1, extendparam2, extendparam3, extendparam4, extendparam5, extendparam6 as extendparam6new, extendparam7, extendparam8, extendparam9, pricefactorname, chargemode, promotionid, promotiontype, resourcespeccsize, resourcespecsizemeasure, spot, releasetype, date_format(substring(convert_tz(createtime,'+00:00','+08:00'),1,19),'%Y%m%d') as dtime from t_billing_feeextendinfo where createtime>= '2024-08-31 16:00:00' and createtime< '20224-09-01 00:00:00'; +----+-------------+-------------------------+---------------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------------+---------------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t_billing_feeextendinfo | p20240814,p20240901 | range | idx_createTime | idx_createTime | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------------------------+---------------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> mysql> explain select feeid, customerid, relid, businessunit, resourcetag, resourcename, resourceid, substring(convert_tz(createtime,'+00:00','+08:00'),1,19) as createtime, substring(convert_tz(updatetime,'+00:00','+08:00'),1,19) as updatetime, extendparam1, extendparam2, extendparam3, extendparam4, extendparam5, extendparam6 as extendparam6new, extendparam7, extendparam8, extendparam9, pricefactorname, chargemode, promotionid, promotiontype, resourcespecsize, resourcespecsizemeasure, spot, releasetype, date_format(substring(convert_tz(createtime,'+00:00','+08:00'),1,19),'%Y%m%d') as dtime from t_billing_feeextendinfo where createtime>= '2024-08-31 16:00:00' and createtime< '2024-08-31 23:59:59'; +----+-------------+-------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t_billing_feeextendinfo | p20240901 | range | idx_createTime | idx_createTime | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) why "where createtime>= '2024-08-31 16:00:00' and createtime< '2024-08-31 23:59:59';" patition is p20240901.
[19 Sep 9:08]
MySQL Verification Team
Hi Mr. xue, Because in order to use index condition , the optimiser requires index statistics, which is in the first partition.
