Don't misunderstand the usage of MySQL in anymore!

Background: A friend in the group said that whether in can be indexed or not is related to the number of strings in in. After the length of the string exceeds, the index will not be used.

Actually, this perception is wrong. Everyone has been deceived by many reprinted articles! ! !

What really affects whether in can be indexed depends on the proportion of qualified data in in, which will affect whether mysql can be indexed!

My previous test showed that more than 40% of the data matched in (this threshold is not necessarily 40%, but my previous test was about 40% and the index would not be used. This is also affected by the mysql version). After that, Start walking the entire table without indexing

Practice:

Figure 1:

Picture 2:

Picture 3:

Picture 4:

Picture 5:

As shown above As shown in the figure, when the impact of table return and index coverage is not considered, when the matching data in in occupies a large amount of the entire table data, a full table scan will be started without indexing.

In addition, special instructions

1. When in is a single value, mysql will automatically optimize to =, so the index will still be used

2. When the columns of in and select are the same, since there is no need to go back to the table, the covering index will be used

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of Don't misunderstand the usage of MySQL in anymore!. For more information, please follow other related articles on the PHP Chinese website!