MySQL Bugs: #116268: if the columns do not form a leftmost prefix of the index can user

Bug #116268	if the columns do not form a leftmost prefix of the index can user
Submitted:	30 Sep 2024 8:16	Modified:	30 Sep 2024 15:57
Reporter:	yijie fu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	if the columns do not form a leftmost prefix of the index can user

Description:
This link ：https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html：”MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index“
its error. 
example when a index contains all columns,MySQL cann use the index to perform lookups if the columns do not form a leftmost prefix of the index!!!

How to repeat:
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`),
  KEY `idx_all` (`id`,`cpid`,`order_id`,`clock_in_type`,`update_time`,`create_time`,`order_distance`,`seller_current_gps`,`service_address_gps`) USING BTREE COMMENT '所有索引'
) ENGINE=InnoDB AUTO_INCREMENT=1478735536405741569 DEFAULT CHARSET=utf8 COMMENT='商家打卡记录表'

sql： explain SELECT * from bjcrm_custom_clockin WHERE order_id = 7174565176523200;

result：user index:"idx_all"

Suggested fix:
”MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index“ is Too absolute. The document needs to be more accurate

Hi Mr. fu,

Thank you for your bug report.

Your example proves that our Documentation is correct.

Column `id` is the leftmost prefix of the index, which can be used for index lookups.

We do not see how  is that description wrong. 

Not a bug.

order_id is listed in the third column of the index. Theoretically, querying based on the order_id will not go through the index, but "explain" result "key" change the index.

Hi Mr. fu,

That part is also explained in our Reference Manual.

Namely, "use index" does not mean that index lookup is used. Not at all !!!!!

USE INDEX means that entire index is scanned. That is a totally different algorithm then index lookup.

Again, this is all explained in our Reference Manual.

Hi bro:
Thank you very much for your answer，but I still have some questions, like the example I gave earlier, using an index but not an index lookup , what algorithm is used to traverse the index tree? Is it a leaf node that performs sequential traversal to scan the entire B+tree?

Just to add, I have read a long document but couldn't find what you said, 'This is all explained in our Reference Manual'
Perhaps due to my poor English, I rely entirely on translation software. Thank you again

Another question, does the key_len in explain means "index used" or "index lookup used","Index Condition Pushdown" about this case How to calculate key_len ?

Another question, does the key_len in explain means "index used" or "index lookup used","Index Condition Pushdown" about this case How to calculate key_len ?

Hi,

Entire index is scanned, sequentially in memory .......

Key length should be the entire width of the index used.

Hi :
“Key length should be the entire width of the index used.”
means  “index tuple” sequentially in memory，The length will be recorded in key_len