First, execute the following sql statement to generate the data required for the test to facilitate the verification of index failure.

create table table_test(
id int(4) primary key auto_increment,
c1 char(10) not null,
c2 char(10) not null,
c3 char(10) not null,
c4 int(4) not null 
);

insert into table_test(c1, c2, c3, c4) values ('11one','12','13',14); 
insert into table_test(c1, c2, c3, c4) values ('21two','22','23',24); 
insert into table_test(c1, c2, c3, c4) values ('31three','32','33',34);
insert into table_test(c1, c2, c3, c4) values ('41four','42','43',44);
insert into table_test(c1, c2, c3, c4) values ('51five','52','53',54);
insert into table_test(c1, c2, c3, c4) values ('51six','52','53',64);

create index c1_c2_c3 on table_test(c1, c2, c3);
show index from table_test;

After the above sql is executed, query all data, and the results are shown in the following figure:

  Then, query all indexes of the table, and the results are as follows:

  Where, c1_c2_c3 is a composite index.

1. The field after like starts with%, and the index is invalid; Index is valid if it does not start with% and ends with%

Execute the following sql statement:

explain select * from table_test where c1 like '%one';

  The results are shown in the figure below:

  According to the results shown in the screenshot, the fields after like start with%, and the index is invalid.

Then execute the following sql statement:

explain select * from table_test where c1 like '11%';

The results are shown in the figure below:

   According to the results shown in the screenshot, the fields after like do not start with% but end with%. The index is valid.

2. If the index is not used before and after the or statement, the index is invalid

Execute the following sql statement. In this statement, c1 is the index and c4 is not the index:

 explain select * from table_test where c1='11one' or c4=24;

The execution results are shown in the following figure:

  The results show that if the index is not used before and after the or statement, the index is invalid.

Execute the following sql statement, in which the composite index fields c1 and c2 are used before and after or:

 explain select * from table_test where c1='11one' or c2='22';

The execution results are shown in the following figure:

  It can be seen from the screenshot that when the fields around or use the fields of composite index, the index fails.

Execute the following sql statement, in which the composite index field c1 and primary key id are used before and after or:

explain select * from table_test where c1='11one' or id='2';

The execution results are shown in the following figure:

  It can be seen from the screenshot that when the fields around or use the index, the index is valid. Note that if the fields around or are the fields of the same composite index, the index is invalid.

3. When the string is not enclosed in single quotation marks, the index is invalid

Execute the following sql statement to the table_ Insert a piece of data into test:

insert into table_test(c1, c2, c3, c4) values ('6111','52','53',64);

  Query all the data in the table, and the results are shown in the following figure:

Execute the following sql statement, in which the field c1 of the composite index is used, but the string is not enclosed in single quotation marks:

 explain select * from table_test where c1=6111;

The execution results are shown in the following figure:

  As can be seen from the screenshot, the index is invalid when the string is not quoted.

4. When combining indexes, if the first column index is not used, the index is invalid

Execute the following sql statement. In this statement, the field C2 of the composite index is used, and the composite index C1 is not used_ c2_ Field in the first column of C3:

explain select * from table_test where c2='12';

The execution results are shown in the following figure:

  5. When combining indexes, the first column index is used, but the subsequent indexes are discontinuous, the subsequent indexes will become invalid

 explain select * from table_test where c1='11one' and c3='13';

Then use only the first column index,

 explain select * from table_test where c1='11one';

The execution results are shown in the following figure:

As can be seen from the screenshot, the keys of the two statements_ Len is 10;

In the first statement, although the c3 field is used, the c3 field is not effective because the c2 field is not used, but c1 is effective.

6. If you use is null or is not null on the index column, the index does not necessarily become invalid

Execute the following sql statement, in which the index column c1 is used and is null is used:

explain select * from table_test where c1 is null;

The execution results are shown in the following figure:

  It can be seen from the screenshot that when the data in the index column is non null, using is null index on the index column is invalid.

Next, let's take a look at the situation where the index does not expire. First, execute the following sql to report to table_ Insert a piece of data with c1 column null into the test table:

alter table table_test modify c1 char(10) default null;
insert into table_test(c2, c3, c4) values ('62', '63', '64');

After execution, query table_ The data in the test table and the results are shown in the following figure:

  You can see the data row with id 8 and the value of c1 is NULL. Next, execute the following sql statement:

 explain select * from table_test where c1 is null;

The execution results are shown in the following figure:

  It can be seen from the query results in the screenshot that when there are NULL records in column c1, the index is not invalidated by using is null as the where condition.

To sum up, using is null or is not null on the index column does not necessarily invalidate the index.

7. Use not, < >,! =, on the index field, Index failure

For not, execute the following sql statement:

explain select * from table_test where c1 not in('11one','21two','31three','41four');

The execution results are shown in the following figure:

  According to the screenshot, the index is invalid.

For < >, execute the following sql statement to establish the index c4:

create index c4 on table_test(c4);

  Execute the following sql statement to verify whether < > is invalid:

  According to the results in the screenshot, when < > is used on the index column, the index does not take effect.

For! =, Execute the following sql statement:

 explain select * from table_test where c4 != 44;

  The execution results are shown in the following figure:

  As can be seen from the screenshot, use! =, on the index column, The index is invalid.

8. When a function is used or a calculation operation is performed on an index column, the index becomes invalid

Execute the following sql statement:

explain select * from table_test where c4/2 = 7;

  The execution results are as follows:

  From the screenshot results, it can be seen that the index is invalid when functions are used or calculation operations are performed on the index column.

epilogue

The above is the case of index failure in mysql. I hope I can help students in need!