How does a MySQL database use indexes exactly?

MySQL databaseMedium, suitable for useIndexMySQL query speed can be greatly improved. But when should I use indexes? When is it not suitable? This article summarizes when the MySQL database uses indexes and when indexes are not needed. Next we will introduce this part.

When does MySQL use indexes?

Use >,=,=,<,<=, if null and BETWEEN for a key code

 
 

  
  
SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;  
  
  
 
  
  
SELECT * FROM table_name WHERE key_part1 IS NULL; 
 
 

When you use LIKE, which does not start with a wildcard

 
 

  
  
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%' 
 
 

Extract rows from another table during join

 
 

  
  
SELECT * from t1,t2 where t1.col=t2.key_part 
 
 

Find the MAX () or MIN () value of the specified index

 
 

  
  
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 
 
 

The prefix of a key code uses order by or GROUP

 
 

  
  
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3 
 
 

The time when all columns used in the query are part of the key code

 
 

  
  
SELECT key_part3 FROM table_name WHERE key_part1=1 
 
 

When MySQL does not use Indexes

If MySQL can predict that it will be faster than scanning the entire table, no index will be used. For example, if key_part1 is evenly distributed between 1 and 100, it is not good to use indexes in the following queries:

 
 

  
  
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90 
 
 

If you use the HEAP table and do not use = to search for all key code parts.

Use order by on the HEAP table.

If you do not use the first part of the key code

 
 

  
  
SELECT * FROM table_name WHERE key_part2=1 
 
 

If you use LIKE, which starts with a wildcard

 
 

  
  
SELECT * FROM table_name WHERE key_part1 LIKE '%jani%' 
 
 

Search for one index and create order by on another index

 
 

  
  
SELECT * from table_name WHERE key_part1 = # ORDER BY key2 
 
 

Here is a summary of the use of indexes and the absence of indexes in MySQL databases. I hope this introduction will be helpful to you!