Mysql 5.7.29

First, let’s set up a table

Table one field default empty string, one field default null

Next we insert 10 pieces of data into the database, execute the following SQL 10 times, and we get the following data

So, do the following two statements go through the index

explain select id, configuration, description from t_my_jvm2 where configuration = '2' ; Explain select ID, configuration, description from T_my_jVM2 where description = 'description';Copy the code

Whether to go index

By looking at the key column of the execution plan, you will get the answer that the top one does not follow the index and the bottom one does the index

This is amazing, why not index the first one ？？？？

Let’s modify the data.

Delete five items of data from configuration and add five items of data to Description

Let’s check it again

Found two columns of data, both indexed

Let’s try one more thing, adding one row to each of the two columns

When I test again, I find that none of the indexes go

Small speculation

So let’s make a quick guess,

# configuration = '2' number of empty strings' < 40% (4 or less in 10), no index. # description = 'description' When the number of null's is less than or equal to 40% (4 out of 10 or less), the index will be removedCopy the code

Tests is NULL and is not NULL

Let’s look at is NULL and is not NULL

Let’s recover the data first

Execute the following two statements

explain select id, configuration, description from t_my_jvm2 where description is null ;

explain select id, configuration, description from t_my_jvm2 where description is not null ;
Copy the code

The result is that the above is NULL statement does not take the index, and the following is not NULL statement takes the index

Let’s test is NULL, and let’s modify the data

Execute again, is null, still won’t move index

Let’s revise the data again

Execute again, is null, discover index missing

At the same time, we execute is not null, find not to walk index

Let’s modify the data,

The index is not null

Modify the data again

Execute again, is not null, find missing index

conclusion

# configuration = '2' number of empty strings' < 40% (4 or less in 10), no index. When the number of null entries is less than or equal to 40% (4 or less out of 10), the number of null entries is greater than 40% (4 or more out of 10), the number of null entries is greater than 40%Copy the code

# is not null If the number of null's is greater than or equal to 30%, the index will be removed. # description = 'description' when the number of null's is less than or equal to 40% (4 out of 10 or less), the index will be removedCopy the code

The last

1, Remember to like as you pass by, friends

2, Nuggets markdown editor is really difficult to use, not as good as Typora, even CSDN, although the content of a website is the most important, but the writing environment is so poor, who can write ！！！！！！！！！！ Ridicule ！！！！