MySQL index use attention to detail

In the query, if you use the LIKE keyword, you should pay attention to the use of the index;

2. Before index is used:

   Mysql> Explain select * FROM employees where first_name= ' Georgi ' \g;
   1. Row ***************************
   Id:1
   Select_type:simple
   Table:employees
   Type:all
   Possible_keys:null
   Key:null
   Key_len:null
   Ref:null
   rows:294382
   Extra:using where
   1 row in Set (0.00 sec)

4. Add Index: CREATE index First_index on employees (first_name);

6. After indexing:

   Mysql> Explain select * FROM employees where first_name= ' Georgi ' \g;
   1. Row ***************************
   Id:1
   Select_type:simple
   Table:employees
   Type:ref
   Possible_keys:first_index
   Key:first_index
   Key_len:16
   Ref:const
   rows:253
   Extra:using where
   1 row in Set (0.00 sec)

7. Use like in

8. Query:

   mysql> explain select * FROM employees where first_name like ' % Georgi ' \g;
   1. Row ***************************
              id:1
     Select_type:simple
           table:employees
             type:all
   Possible_keys:null
              Key:null
         key_len:null
              ref:null
            rows:294382
            extra:using where
   1 row in Set (0.05 sec)

10. Mysql> Explain select * FROM employees where first_name like '%georgi% ' \g;
    1. Row ***************************
               id:1
      Select_type:simple
            table:employees
              type:all
    Possible_keys:null
               Key:null
          key_len:null
               ref:null
             rows:294382
             extra:using where
    1 row in Set (0.00 sec)
    

12. Mysql> Explain select * FROM employees where first_name like ' georgi% ' \g;
    1. Row ***************************
    Id:1
    Select_type:simple
    Table:employees
    Type:range
    Possible_keys:first_index
    Key:first_index
    Key_len:16
    Ref:null
    rows:253
    Extra:using where
    1 row in Set (0.00 sec)

Can be found only after the query keyword plus% can be used to index;

There are also no indexes that are used by null,<> that are not available:

Mysql> Explain select * FROM employees where first_name are not null\g;
1. Row ***************************
Id:1
Select_type:simple
Table:employees
Type:all
Possible_keys:first_index
Key:null
Key_len:null
Ref:null
rows:294382
Extra:using where
1 row in Set (0.00 sec)

Mysql> Explain select * FROM employees where first_name <> ' Georgi ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:employees
Type:all
Possible_keys:first_index
Key:null
Key_len:null
Ref:null
rows:294382
Extra:using where
1 row in Set (0.00 sec)

If a function or operation is used above the column of the index, it cannot be referenced to the index:

Mysql> Explain select * FROM Employees where UPPER (first_name) = ' Ult ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:employees
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:294382
Extra:using where
1 row in Set (0.00 sec)

When using composite indexes, also note that the first column is not in the where;

Correct use of the index:

Mysql> Explain select * FROM employees where first_name= ' Georgi ' and Last_name= '
Facello ' \g;
1. Row ***************************
           id:1
  Select_type:simple
        table:employees
          type:ref
Possible_keys:name_index
           key:name_index
      key_len:34
           ref:const,const
         rows:2
         extra:using where
1 row in Set (0.00 sec)

Mysql> Explain select * FROM employees where first_name= ' Georgi ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:employees
Type:ref
Possible_keys:name_index
Key:name_index
Key_len:16
Ref:const
rows:253
Extra:using where
1 row in Set (0.00 sec)

Not used in the index:

Mysql> Explain select * FROM employees where last_name= ' Facello ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:employees
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:300252
Extra:using where
1 row in Set (0.00 sec)

Mysql> Explain select * FROM employees where last_name= ' Facello ' or first_name= '
Georgi ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:employees
Type:all
Possible_keys:name_index
Key:null
Key_len:null
Ref:null
rows:300252
Extra:using where
1 row in Set (0.00 sec)

The second example is that in the where it is only a separate search for first_name because of the use of OR.