Transferred from: http://blog.chinaunix.net/uid-29305839-id-4257512.html
Create a multi-column index:
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)
);
To create multiple indexes:
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)
);
When the conditions of a query statement contain last_name and first_name, the
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 then filter the records first_name 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 ' and first_name < ' N ';
Query statements in the following form do not take advantage of multiple column indexes:
SELECT * FROM Test WHERE first_name= ' Michael ';
SELECT * 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.
MySQL column index and multi-column index (Federated Index)
