Bug #116321 Index push down may lead to performance degradation

• Submitted: 9 Oct 10:34
• Modified: 10 Oct 10:57
• Reporter: yijie fu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.0.1-1.el9
• OS: MacOS (14.5 (23F79))
• CPU Architecture: Any (Apple M1)

[9 Oct 10:34] yijie fu

Description:
1:SELECT * from bjcrm_custom_clockin WHERE cpid =247754680960946 and order_id = 4736980658321856; 
this sql search no data.
2:explain    SELECT * from bjcrm_custom_clockin WHERE cpid =247754680960946 and order_id = 4736980658321856;
key-len = 16 
3:explain    SELECT * from bjcrm_custom_clockin WHERE cpid =247754680960946 and order_id = 4736980658321856 AND clock_in_type >1;
key-len = 20、 Using index condition

Why does adding other query conditions to SQL queries without data result in longer index lengths being used？

table:
CREATE TABLE `bjcrm_custom_clockin` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cpid` bigint(40) NOT NULL DEFAULT '0' COMMENT '商家id',
  `order_id` bigint(40) NOT NULL DEFAULT '0' COMMENT '订单id',
  `clock_in_type` int(4) NOT NULL DEFAULT '-1' COMMENT '打卡类型（1：开始卡，2结束卡）',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `order_distance` int(10) NOT NULL DEFAULT '0' COMMENT '打卡时距离订单的距离',
  `seller_current_gps` varchar(100) NOT NULL DEFAULT '' COMMENT '打卡时当前位置的gps经纬度，lon,lat',
  `service_address_gps` varchar(100) NOT NULL DEFAULT '' COMMENT '订单的gps',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_orderid_cpid_type` (`order_id`,`cpid`,`clock_in_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1478735536405741569 DEFAULT CHARSET=utf8 COMMENT='商家打卡记录表'

How to repeat:
I think there are two possibilities for this situation: 1. There is a problem with the key len calculation; 2. The index push down is not perfect enough

Suggested fix:
Choose the best performing option

[9 Oct 11:57] MySQL Verification Team

Hi Mr. fu,

Thank you for your bug report.

However, version 5.7 is no longer supported.

Unsupported.

[10 Oct 2:31] yijie fu

There is the same issue in this version

[10 Oct 3:03] yijie fu

version 9.0.1-1.el9 is the same as 5.7.

[10 Oct 9:46] MySQL Verification Team

Hi Mr. fu,

We ran your test case and got the following results:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	bjcrm_custom_clockin	NULL	ref	un_orderid_cpid_type	un_orderid_cpid_type	16	const,const	1	100.00	NULL

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	bjcrm_custom_clockin	NULL	range	un_orderid_cpid_type	un_orderid_cpid_type	20	NULL	1	100.00	Using index condition

This is expected behaviour, since the optimiser plans are different.

This is all explained in our Reference Manual.

Not a bug.

[10 Oct 10:32] MySQL Verification Team

Hi,

Some final explanations ........

For the reference search, only first two columns of the index were needed, while for the range search, the entire index is required.

That is very well known manner in resolving these two different plans.

Not a bug.

[10 Oct 10:57] yijie fu

Hi 
 sorry this is not a bug. I am wrong.
 but,Why do search criterias have to be used？！
 
 search criterias 'WHERE cpid =247754680960946 and order_id = 4736980658321856 AND clock_in_type >1' change to 'WHERE cpid =247754680960946 and order_id = 4736980658321856' ,the key lenth while be shorter. Does it mean that the chosen execution plan is better?

Is it necessary to filter out the query criteria when selecting the execution plan and then choose the optimal execution plan?

[10 Oct 13:06] MySQL Verification Team

Hi,

The answer is simple: Yes, it is !!!!