MySQL index left prefix matching rules

MySQL index leftmost principle principle and code examples

In MySQL, indexing is one of the important means to improve query efficiency. Among them, the index leftmost principle is an important principle that we need to follow when using indexes to optimize queries. This article will introduce the principle of the leftmost principle of MySQL index and give some specific code examples.

1. The principle of index leftmost principle

The index leftmost principle means that in an index, if the query condition is composed of multiple columns, then only the leftmost index in the index Only by querying side columns can you make full use of the index. Specifically, when querying, only by matching according to the leftmost column in the index, can the orderliness in the index be used. If the non-leftmost column in the index is used for query, the orderliness of the index cannot be used. , the query efficiency will be affected.

2. Example of the index leftmost principle

In order to better understand the index leftmost principle, let’s make an example. Assume that there is the following table student:

+----+-------+--------+----------+
| id | name  | gender | birthday |
+----+-------+--------+----------+
|  1 | John  | Male   | 2001-02-01 |
|  2 | Mary  | Female | 2002-03-02 |
|  3 | Tom   | Male   | 2003-04-03 |
|  4 | Alice | Female | 2004-05-04 |
+----+-------+--------+----------+

Create an index:

CREATE INDEX idx_student ON student(name, gender, birthday);

According to the index leftmost principle, we can use the following method to query:

1. Use Query the leftmost column in the index:

SELECT * FROM student WHERE name = 'John';

This query can make full use of the index, and the query efficiency will be relatively high.

2. Use the two leftmost columns in the index to query:

SELECT * FROM student WHERE name = 'John' AND gender = 'Male';

This query can also make full use of the index, because the column order in the query condition and the The column order is consistent.

3. Use the three leftmost columns in the index for query:

SELECT * FROM student WHERE name = 'John' AND gender = 'Male' AND birthday = '2001-02-01';

This query can also make full use of the index and conforms to the leftmost index principle.

However, if we use a query method that does not comply with the leftmost principle of the index, such as:

SELECT * FROM student WHERE gender = 'Male';

This query cannot make full use of the index, because the columns in the query conditions are not in accordance with the index. Querying in the leftmost column will reduce query efficiency.

3. Summary

The index leftmost principle is a principle we need to follow when using MySQL indexes to optimize queries. By querying in the order of the leftmost column in the index, we can make full use of the orderliness of the index and improve query efficiency. If you use a query method that does not comply with the leftmost index principle, the query efficiency will be reduced. Therefore, when designing the database, it is necessary to create appropriate indexes according to specific query requirements, and when writing query statements, query according to the leftmost index principle, thereby improving query efficiency.

The above is an introduction to the principles and code examples of the leftmost principle of MySQL indexes. I hope it will be helpful to everyone.

The above is the detailed content of MySQL index left prefix matching rules. For more information, please follow other related articles on the PHP Chinese website!