MySQL loose index Scan (Loose index Scan) _mysql

The most effective way to optimize group by IS to use the index directly to get the field that requires the group completely. When using this access method, MySQL uses the type of index that the keyword sorts (for example, the Btree index). This allows the fields in the index to be used for group not to fully cover the key in the Where Condition index. Because it contains only a portion of the keywords in the index, it is called a loose index scan.

Historically MySQL cannot do a loose index scan, this way can scan the noncontiguous part of the index, assuming that in the following example, there is an index on the column (A,B) to run the following query:

Mysql> SELECT ... From TBL WHERE B BETWEEN 2 and 3;

Because the index starts with column A, but where no column a,mysql will do a full table scan to eliminate the mismatched rows.
Would like to have a quick way to execute the query. Some index structures (not MySQL) let you navigate to the beginning of each range, scan to the end of the range, and then jump to the beginning of the next range.
This is, of course, a relatively simple example, and it can easily be achieved by adding a different index. In fact, many times adding another index does not necessarily solve the problem. One example is that some queries have a range condition on the first column of the index with an equivalent condition on the second column.
Starting with MySQL 5.0, loose index scans are possible on certain conditions, such as finding the maximum and minimum values in a grouped query:

Mysql> EXPLAIN SELECT actor_id, MAX (film_id)
-> from Sakila.film_actor
-> GROUP by ACTOR_IDG
1. Row ***************************
Id:1
Select_type:simple
Table:film_actor
Type:range
Possible_keys:null
Key:primary
Key_len:2
Ref:null
rows:396
Extra:using Index for Group-by

You can use loose index scans in some of the following cases:
• The query is for a single table.
GROUP by includes the 1th contiguous portion of the index (if the query has a distinct clause for GROUP by, all distinct properties point to the beginning of the index).
• Use only the cumulative function (if any) MIN () and Max (), and they all point to the same column.
• Any other part of the index (except those that are referenced from the query) must be a constant (that is, they must be referenced by the number of constants), but the min () or Max () function has an exception to the arguments.