Bug #113404 Loose Index Scan limitations

• Submitted: 13 Dec 2023 10:19
• Modified: 13 Dec 2023 11:47
• Reporter: Jinhao Zhan
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 8.0.22
• OS: Any
• CPU Architecture: Any
• Tags: loose index scan

[13 Dec 2023 10:19] Jinhao Zhan

Description:
I want to use Loose Index Scan。 But MySQL use Tight Index Scan actually。

An Index idx(airport, flight_no) in table t_board_requirement(id, airport, flight_no, ...)

My sql is : select flight_no from t_board_requirement where airport = 'EHU' group by flight_no; （the column of where clause is the first part of index）

There is a case in MySQL 8.0 Reference Manual (https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html) about Tight Index Scan：
The `GROUP BY` does not begin with the first part of the key, but there is a condition that provides a constant for that part: 
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

So My sql use Tight Index Scan is correct according to the reference manual。

But the below SQL can use Loose Index Scan: 
1. select flight_no from t_board_requirement group by airport, flight_no;
2. select flight_no from t_board_requirement where airport = 'EHU' group by airport, flight_no;

My Question is：Why the first SQL cannot use Loose Index Scan。

MySQL can use Loose Index Scan group by with columns : airport and flight_no. Why it cannot again use Index Condition Pushdown to filter result that airport = 'EHU'?

And I have found a blog about how MySQL implement Loose Index Scan : https://dev.mysql.com/blog-archive/what-is-the-scanning-variant-of-a-loose-index-scan/.

As the description from the blog. MySQL only need stop skipping when the airport column != 'EHU', then the first sql can use Loose Index Scan.

So according to my understanding, There are two ways to implement Loose Index Scan that make my first sql use Loose Index Scan.

I am not familiar with C language, so I am unable to review the source code to analyze the issue. Can somebody give me some ideas?

Thank you !

How to repeat:
the case from the Reference Manual can repeat my question.

https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html

[13 Dec 2023 11:47] MySQL Verification Team

Hi Mr. Zhan,

Thank you for your bug report.

However, we are not able to repeat your bug report.

We need to have your table definition and table contents. We also need the outputs from:

* EXPLAIN EXTENDED SELECT .......

* EXPLAIN ANALYSE SELECT .........

We also DEFINITELY can not accept report based on the ancient 8.0.22. Please us 8.0.35 instead !!!

Next, please make sure that all the conditions for the Loose versus Tight index scan have to be fully fulfilled.

We can not see that from your report. We need to be able to repeat the behaviour that you report , but  with 8.0.35 release.

Can't repeat.