Create a table that uses the column index: CREATE TABLE Index_test1 (
ID int NOT NULL auto_increment,
first_name varchar (+) NOT NULL,
last_name varchar (+) NOT NULL,
Primary key (ID),
Index Index_first (first_name),
Index Index_last (last_name)
) engine MyISAM charset UTF8;
Create a table that uses multiple-column indexes:
CREATE TABLE More_index_test1 (
ID int NOT NULL auto_increment,
first_name varchar (+) NOT NULL,
last_name varchar (+) NOT NULL,
Primary key (ID),
Index Index_first (first_name,last_name)
) engine MyISAM charset UTF8; 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 index_test where first_name = ' 1 ' and first_name = ' 2 '; 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 more_index_test1 WHERE first_name = ' 1 ';
SELECT * from more_index_test1 WHERE first_name = ' 1 ' and first_name = ' 2 ';
SELECT * from more_index_test1 WHERE first_name = ' 1 ' and (first_name = ' 2 ' OR first_name = ' 3 ');
SELECT * from more_index_test1 WHERE first_name = ' 1 ' and first_name >= ' 2 ' and First_Name < ' 3 ';However, queries in the following form cannot use multi-column indexes: SELECT * from more_index_test1 where last_name = ' 1 '; SELECT * from More_index_test1 WHERE last_name= ' 1 ' OR first_name= ' 2 ';
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.
Analysis of MySQL column index and multi-column index
