Loose index scan and loosescan

Loose index scan and loosescan

The most effective way to optimize Group By is to directly use indexes to completely obtain the fields that require group. When this access method is used, MySQL uses the index type (such as the BTREE index) that sorts the keywords ). This makes it unnecessary for the group field in the index to completely cover the key corresponding to the index in the WHERE condition. Because it only contains part of the keywords in the index, it is called a loose index scan.

In history, MySQL cannot perform loose index scanning. This method can scan the non-consecutive parts of an index. In the following example, there is an index on the column (a, B, run the following query:

Mysql> SELECT... FROM tbl WHERE B BETWEEN 2 AND 3;

Because the index starts from column a, but column a is not specified in the WHERE clause, MySQL performs a full table scan to eliminate unmatched rows.
We hope to have a quick way to execute the query. Some Index Structures (not MySQL) Let you locate the beginning of each range, scan to the end of the range, and then jump to the start of the next range.
This is of course a relatively simple example, and it is easy to add a different index to achieve the goal. In fact, adding another index may not solve the problem. In one example, some queries have range conditions on the first column of the index and have equivalent conditions on the second column.
From MySQL 5.0, loose index scanning is possible under certain conditions, such as finding the maximum and minimum values in grouping queries:

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-

In the following situations, you can use loose index scanning:
• Query for a single table.
• Group by includes the 1st consecutive parts of the index (if a group by clause is queried, all DISTINCT attributes point to the beginning of the index ).
• Only use the cumulative functions (if any) MIN () and MAX (), and they all point to the same column.
• Any other part of the index (except those from group by referenced in the query) must be a constant (that is, they must be referenced BY the number of constants), but MIN () or MAX () function parameter exceptions.