MySQL database provides two types of indexes, if not set correctly, the efficiency of the index will be greatly compromised but the problem is completely unknown.
Copy Code code 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,first_name)
);
The above is actually a multiple-column index, and the code to create the column index is as follows:
Copy Code code 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 multiple-column index can be considered a sorted array that contains values created by the merged (CONCATENATE) indexed column values. When the condition of a query statement contains last_name and first_name, for example:
Copy Code code as follows:
SELECT * FROM Test WHERE last_name= ' Kun ' and first_name= ' Li ';
SQL will first filter out Last_Name records that match the criteria, and on the basis of filtering first_name records that match the criteria. So if we were to create two column indexes on last_name and first_name respectively, MySQL would be handled differently, and it would choose one of the most restrictive indexes to retrieve, which could be understood as the index to retrieve the most powerful, the other to be used, This is less effective than a multiple-column index.
However, the use of multiple-column indexes also requires conditions, and the following forms of query statements can take advantage of multiple-column indexes:
Copy Code code as follows:
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 form of query statements does not utilize multiple-column indexes:
Copy Code code as follows:
SELECT * FROM Test WHERE first_name= ' Michael ';
SELECT * FROM Test WHERE last_name= ' Widenius ' or first_name= ' Michael ';
Dolegian indexes are more advantageous than indexing each column, because the more indexes are built, the more disk space is, and the slower the data is when it is updated.
In addition to the establishment of a multiple-column index, the order is also to be noted, the strict index should be placed in the front, so that the intensity of the screening will be greater and more efficient.
