Detailed description of mysql joint index _ MySQL

Detailed description of mysql joint index bitsCN.com

Detailed description of mysql joint index

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.

BitsCN.com