Detailed description of mysql joint index

Source: Internet
Author: User
Welcome to the Linux community forum and interact with 2 million technical staff to enter the joint index, also known as composite index. For composite index: Mysql uses the fields in the index from left to right. One query can only use one part of the index, but only the leftmost part. For example, the index is keyindex (a, B, c ). Supports a | a, B | a, B, and c3 combinations.

Welcome to the Linux community forum and interact with 2 million technical staff> join index is also called compound index. For composite index: Mysql uses the fields in the index from left to right. One query can only use one part of the index, but only the leftmost part. For example, the index is key index (a, B, c ). Supports a | a, B | a, B, and c combinations.

Welcome to the Linux community forum and interact with 2 million technicians>

A joint index is also called a composite index. For composite index: Mysql uses the fields in the index from left to right. One query can only use one part of the index, but only the leftmost part. For example, the index is key index (a, B, c ). Supports a | a, B | a, B, and c combinations for search, but does not support search by B and c. when the leftmost field is a constant reference, the index is very effective.

The index of two or more columns is called a composite index.

You can narrow the search range by using the additional columns in the index, but using an index with two columns is different from using two separate indexes. The structure of the composite index is similar to that of the phone book. The name of a person consists of a surname and a name. The phone book is first sorted by the last name, and then sorted by the name of the person with the same surname. If you know the last name, the phone book will be very useful; if you know the last name and the first name, the phone book will be more useful, but if you only know the first name but not the last name, the phone book will be useless.

Therefore, when creating a composite index, you should carefully consider the column sequence. Composite indexes are useful when you search all the columns in an index or only the first few columns. composite indexes are useless when you only search for any of the following columns.

For example, create a compound index of name, age, and gender.

Create table test (

A int,

B int,

C int,

KEY a (a, B, c)

);

Excellent: select * from test where a = 10 and B> 50

Difference: select * from test where a50

Excellent: select * from test order by

Difference: select * from test order by B

Difference: select * from test order by c

Excellent: select * from test where a = 10 order by

Excellent: select * from test where a = 10 order by B

Difference: select * from test where a = 10 order by c

Excellent: select * from test where a> 10 order by

Difference: select * from test where a> 10 order by B

Difference: select * from test where a> 10 order by c

Excellent: select * from test where a = 10 and B = 10 order by

Excellent: select * from test where a = 10 and B = 10 order by B

Excellent: select * from test where a = 10 and B = 10 order by c

Excellent: select * from test where a = 10 and B = 10 order by

Excellent: select * from test where a = 10 and B> 10 order by B

Difference: select * from test where a = 10 and B> 10 order by c

Indexing principles

1. The fewer indexes, the better.

Cause: when modifying data, the first index must be updated to reduce the write speed.

2. Place the narrower field on the left of the key.

3. Avoid file sort sorting, temporary tables, and table scanning.

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.