MySQL loose index scan

MySQL loose index scan

As we all know, InnoDB uses the index organization table (IOT), which is called the index organization table, and the leaf node stores all the data, which means that the data is always stored in a certain order. So the question is, what should be the execution of such a statement? The statement is as follows:

Select count (distinct a) from table1;

If column a has an index, how can we scan it based on simple ideas? It is very easy to scan one by one. In this way, a full index scan is actually performed, which is very inefficient. This scan method will scan many duplicate indexes. In this case, the optimization method is also easy to think of: Skip duplicate indexes. So we can find such an optimization method on the Internet:

Select count (*) from (select distinct a from table1) t;

From the information that has been searched, the extra in the execution plan changes from using index to using index for group-.

However, but, fortunately we have not used version 5.1, and most of them are Version 5.5 or above. These modern versions have implemented loose index scan:

It's good. You don't need to use this kind of tricks to optimize SQL.

The group by statement in this document is a bit interesting. The most popular method is to scan the entire table and create a temporary table, so that the execution plan will be ugly, there must be ALL and using temporary tables.

This article permanently updates the link address: