Typical scenarios where indexes can be used in MySQL

Typical scenarios where indexes can be used in MySQL
(1) Match full value;
(2) The range of matching values query, the value of the index can be scoped to find;
(3) match the leftmost prefix, using only the leftmost column in the index to find;
(4) The query is more efficient when querying the index only and querying the columns in the indexed fields;
(5) match the column prefix, using only the first column in the index, and only the first part of the index column to find;
(6) can achieve the index matching part of the exact and other parts of the scope of query;
(7) If the column name is an index, then use column_name is null to use the index (different from Oracle)

Below is an example of T_user

CREATE table T_user (ID integer NOT NULL auto_increment primary key, name varchar (in), Salary decimal (7, 2), bonus decimal (7,2), sex tinyint (1));

ALTER TABLE T_user Add index Index_name_salary_bonus (name, salary, bonus);

(1) SELECT * from t_user where name = ' Zhangsan ';

Mysql> Explain select * from t_user where name = ' Zhangsan ' \g;
*************************** 1. Row ***************************
            id:1
  select_type:simple
        Table:t_user
         type:ref
Possible_keys:index_name_salary_bonus
          Key:index_name_salary_bonus
       key_len:93
          ref:const
          rows:1
        extra:using where
1 row in Set (0.00 sec)

ERROR:
No query specified

Mysql> Explain select * from T_user where name like '%zhangsan ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t_user
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:3
Extra:using where
1 row in Set (0.00 sec)

ERROR:

No query specified

(2) SELECT * from t_user where name = ' Zhangsan ' and salary between 1.2 and 2.4;

Mysql> Explain select * from t_user where name = ' Zhangsan ' and salary between 1.2 and 2.4 \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t_user
Type:range
Possible_keys:index_name_salary_bonus
Key:index_name_salary_bonus
Key_len:98
Ref:null
Rows:1
Extra:using where
1 row in Set (0.00 sec)

ERROR:
No query specified
(3) SELECT * from T_user where salary between 1.2 and 2.4;

Mysql> Explain select * from T_user where salary between 1.2 and 2.4 \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t_user
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:3
Extra:using where
1 row in Set (0.00 sec)

ERROR:
No query specified
(4) Select name from T_user where salary between 1.2 and 2.4;
Mysql> explain select name from T_user where salary between 1.2 and 2.4 \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t_employee
Type:range
Possible_keys:idx_salary_bonus
Key:idx_salary_bonus
Key_len:5
Ref:null
Rows:1
Extra:using where
1 row in Set (0.00 sec)

ERROR:
No query specified

(5) Select name from T_user where name is like ' zhang% ';

Mysql> explain select name from T_user where name like ' zhang% ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t_employee
Type:range
Possible_keys:idx_salary_bonus
Key:idx_salary_bonus
Key_len:5
Ref:null
Rows:1
Extra:using where
1 row in Set (0.00 sec)

ERROR:
No query specified

(6) SELECT * from t_user where name = ' Zhangsan ' and sex like '%1% ';

Mysql> Explain select * from t_user where name = ' Zhangsan ' and sex like '%1% ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t_employee
Type:ref
Possible_keys:idx_salary_bonus
Key:idx_salary_bonus
Key_len:5
Ref:const
Rows:1
Extra:using where
1 row in Set (0.00 sec)

ERROR:
No query specified

(7) SELECT * from T_user where name is null;

Mysql> Explain select * from T_user where name is null \g
1. Row ***************************
Id:1
Select_type:simple
Table:t_employee
Type:ref
Possible_keys:idx_salary_bonus
Key:idx_salary_bonus
Key_len:5
Ref:const
Rows:1
Extra:using where
1 row in Set (0.00 sec)

Typical scenarios where indexes can be used in MySQL