8.3.5 multiple-column Indexes Multiple-column index
MySQL can create a conforming index (indexed on multiple columns), an index can contain as many as 16 columns, and for some data types you can index a prefix column.
MySQL can use a multiple-column index for querying, test all columns in the index, or query only test the first column, first 2 columns, first 3 columns.
If you specify the correct order at the time of the index definition, a simple conforming index can speed up several queries.
A multiple-column index is considered to be a sorted array with rows of values in the index
Attention:
As a composite index, you can introduce hashed columns based on other columns. If this column is short, reasonable only, and can be indexed,
may be faster than a wide index on many columns. In MySQL, it is easy to use this extra column:
SELECT * from Tbl_name
WHERE hash_col=md5 (CONCAT (VAL1,VAL2))
and Col1=val1 and Col2=val2;
Suppose a table has the following rules:
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 name index is in the last_name and first_name columns, which can be used to find query-specific values that combine last_name and first_name values within a known range.
It can also be used for queries of a particular last_name value, because the last_name column is the leftmost prefix of the index.
Therefore, name index applies to the following query:
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 ';
Name index cannot be used for the following query:
SELECT * FROM Test WHERE first_name= ' Michael ';
SELECT * FROM Test
WHERE last_name= ' Widenius ' or first_name= ' Michael ';
Suppose you execute the following SELECT statement:
SELECT * from Tbl_name
WHERE Col1=val1 and Col2=val2;
If a multiple-column index exists on col1 and col2, the corresponding record can be obtained directly. If a single Single-column index is in col1 and col2,
The optimizer tries to use the index merge optimization or tries to find the most qualified index by determining which index excludes the most rows.
If the table has more than one column index, the leftmost prefix of the index can be used to find records through the optimizer. Like what
If you have a 3-column index in (col1, col2, col3), you can search through the index (col1), (col1, col2), and (col1, col2, col3).
MySQL cannot use the index to perform lookups, and if the column does not form the leftmost prefix of the index to find, suppose you have the selection statement shown here:
SELECT * from Tbl_name WHERE col1=val1;
SELECT * from Tbl_name WHERE col1=val1 and Col2=val2;
SELECT * from Tbl_name WHERE col2=val2;
SELECT * from Tbl_name WHERE col2=val2 and Col3=val3;
If a index exists in (col1, col2, col3), only the first 2 can use the index, and the third and fourth queries
The indexed column is indeed involved, but T (col2) and (col2, col3) are not the leftmost columns of the index.