8.3.5 multiple-column Indexes Multiple-column index

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.