Description of the time when MySQL creates an index

The following articles mainly introduce the time when MySQL indexes are created, that is, in actual operations, when MySQL databases cannot be read to create indexes, the following is a detailed description of the article. I hope you will have a better understanding of the content after browsing.

Now we have learned how to create an index. Under what circumstances do we need to create an index? IN general, you need to create an index for the columns that appear IN the WHERE and JOIN operations, but this is not the case because MySQL only applies to <, <=, =,>,> =, BETWEEN, IN, and sometimes LIKE will use the index. For example:

 
 

  
  
SELECT t. Name FROM mytable t left join mytable m ON t. Name = m. username WHERE m. age = 20 AND m. city = 'zhengzhou'
 
 

In this case, you need to create an index for the city and age. Because the userame of the mytable table also appears in the JOIN clause, it is also necessary to create an index for MySQL.

As mentioned earlier, only LIKE needs to be indexed in some cases. MySQL does not use an index when it starts with the wildcard "%" and. For example, the following sentence uses an index:

 
 

  
  
SELECT * FROM mytable WHERE username like'admin%' 
 
 

The following statement will not be used:

 
 

  
  
SELECT * FROM mytable WHEREt Name like'%admin'  
 
 

Therefore, pay attention to the above differences when using LIKE. The above content is an introduction to the time when MySQL indexes are created. I hope you will get some benefits.