Correctly understand column indexes and multi column indexes in Mysql

The Mysql database provides two types of indexes, and if you don't set them up correctly, they can be very inefficient without knowing what the problem is.

CREATE TABLE test (
  id     INT NOT NULL,
  last_name CHAR(30) NOT NULL,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (id),
  INDEX name (last_name,first_name)
);

What is created above is actually a multi-column index. The code to create the column index is as follows:

CREATE TABLE test (
  id     INT NOT NULL,
  last_name CHAR(30) NOT NULL,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (id),
  INDEX name (last_name),
   INDEX_2 name (first_name)
);

A multi-column index can be thought of as a sorted array containing the values created by combining (concatenate) index column values. When the condition of the query statement contains last_name and first_name, for example:

SELECT * FROM test WHERE last_name='Kun' AND first_name='Li';

sql will filter out the qualified records of last_name, and then filter the qualified records of first_name. If we create two column indexes on last_name and first_name respectively, mysql will choose one of the most rigorous indexes to retrieve, which can be understood as the index with the strongest retrieval ability to retrieve, and the other one is not available, so the effect is not as good as the multi-column index.

However, the utilization of multi-column index is also required. The following form of query statement can make use of multi-column index:

SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';

The following forms of query statements do not make use of multi-column indexes:

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

Multi-column indexing has an advantage over indexing each column separately, because the more indexes are built, the more disk space is taken up and the slower the data is updated.
In addition, when establishing multi-column indexes, the order also needs to pay attention to, should put the strict index in front, so that the filtering force will be greater, more efficient.