Correct understanding of MySQL's column index and multi-column index

This article transferred from: http://blog.csdn.net/lovelyhermione/article/details/4580866

The MySQL database provides two types of indexes, and if not set correctly, the efficiency of the index is compromised but the problem is completely unknown.

CREATE TABLE Test (      ID         INT NOT NULL,      last_name  CHAR (+) not NULL,      First_ Name CHAR (+) not NULL,      PRIMARY KEY (id),      INDEX name (last_name,first_name)  );  

The 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 (+) not NULL,      First_ Name CHAR (+) not NULL,      PRIMARY KEY (id),      INDEX name (last_name),       index_2 name (first_ name)  );  

A multicolumn index can be thought of as a sorted array that contains values created by merging (CONCATENATE) indexed column values. When the conditions of a query statement include last_name and first_name, for example:

SELECT * FROM Test WHERE last_name='Kun' and first_name='Li ';

SQL will first filter out records that match the last_name criteria, and on top of them, filter first_name to match the criteria. Well, if we were to create two column indexes on last_name and first_name, MySQL would be treated differently, it would choose the most rigorous index to retrieve, it would be understood to be the most powerful index to retrieve, and the other one could not be used. This would be less effective than a multi-column index.

However, the use of multi-column indexes is also conditional, and the following forms of query statements can take advantage of the multiple-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'< first_name'N';

Query statements in the following form do not take advantage of multiple column indexes:

SELECT * FROM Test WHERE first_name='Michael';     * FROM Test WHERE last_name='widenius' OR first_name='Michael ';  

Dolegian indexes are more advantageous than indexes on each column, because the more indexes are built up, the more disk space is available, and the slower the data is to be updated.

In addition to multi-column indexing, the order is also important to note, the strict index should be placed in front, so that the intensity of filtering will be greater and more efficient.

Correct understanding of MySQL's column and multicolumn indexes