Interview Blitz 60: What would cause a MySQL index to fail?

Create test tables and data


In order to demonstrate and test which case will cause the index to fail, we first create a test table and corresponding data:
-- create table
drop table if exists student;
create table student(
id int primary key auto_increment comment 'primary key',
sn varchar(32) comment 'student number',
name varchar(250) comment 'name',
age int comment 'age',
sex bit comment 'sex',
address varchar(250) comment 'home address',
key idx_address (address),
key idx_sn_name_age (sn,name,age)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- add test data
insert into student(id,sn,name,age,sex,address)
values(1,'cn001','Zhang San',18,1,'Gao Lao Zhuang'),
(2,'cn002','Li Si',20,0,'Huaguoshan'),
(3,'cn003','Wang Wu',50,1,'Water Curtain Cave');

There are a total of 3 indexes in the current table, as shown in the following figure:


PS: The following content in this article is based on the MySQL 5.7 InnoDB data engine.



Index invalidation case 1: non-leftmost match
The leftmost matching principle means that the joint index can be used for the query starting from the leftmost field, otherwise the joint index cannot be used.
The field order of our joint index is sn + name + age, we assume that their order is A + B + C, the following joint index usage is as follows:

As can be seen from the above results, if the fields matching from the leftmost can use the upper joint index, for example:


A+B+C


A+B


A+C

Where: A equals the field sn, B equals the field name, and C equals the field age.



However, B+C cannot use the joint index, which is the leftmost matching principle.
Index Invalidation Scenario 2: Error Fuzzy Query
There are 3 common uses of fuzzy query like:

Any character after fuzzy matching: like 'Zhang%'
Fuzzy match any preceding character: like '% Zhang'
Any character before and after fuzzy matching: like '%zhang%'

Among these three fuzzy queries, only the first query method can use the index, and the specific execution results are as follows:

Index Failure Case 3: Column Operations
If the index column uses an operation, the index will also fail, as shown in the following figure:

Index Invalidation Scenario 4: Using Functions
If the query column uses any function provided by MySQL, the index will become invalid. For example, the execution plan after using the ifnull function in the following column is as follows:

Index invalidation case 5: type conversion
If the index column has type conversion, then the index will not be used. For example, address is a string type, and setting a value of type int during query will cause the index to fail, as shown in the following figure:

Index invalidation case 6: use is not null
When is not null is used in the query, the index will also become invalid, and is null will trigger the index normally, as shown in the following figure:

Summarize
There are six common scenarios that cause MySQL indexes to fail:

The union index does not satisfy the leftmost matching principle.
The fuzzy query is preceded by an uncertain matching character.
Index columns are involved in the operation.
Index columns use functions.
There is type conversion for indexed columns.
Indexed columns are queried using is not null.

Related Articles

Explore More Special Offers

  1. Short Message Service(SMS) & Mail Service

    50,000 email package starts as low as USD 1.99, 120 short messages start at only USD 1.00