Sample code sharing for MySQL using joint index function

This article mainly introduces the function and usage of MySQL joint index, and analyzes the concept, function, specific usage and related precautions of joint index based on specific examples. Friends in need can refer to this article

Examples describe the functions and usage of MySQL joint index. Share it with everyone for your reference, the details are as follows:

Joint index is also called composite index. For composite indexes: Mysql uses the fields in the index from left to right. A query can only use part of the index, but only the leftmost part. For example, the index is key index (a,b,c). It can support 3 combinations of a | a,b| a,b,c for search, but does not support b,c for search. When the leftmost field is a constant reference , the index is very effective.

An index on two or more columns is called a composite index.

With additional columns in the index, you can narrow the scope of your search, but using an index with two columns is different than using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name is composed of a first and last name. The phone book is first sorted by last name pairs, and then sorted by first name for people with the same last name. A phone book is very useful if you know your last name, more useful if you know both your first and last name, but useless if you only know your first name but not your last name.

So when creating a composite index, you should carefully consider the order of columns. Composite indexes are useful when searching on all columns in the index or only on the first few columns; they are not useful when searching on any subsequent columns.

For example: Create a composite index of name, age, and gender.

create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);

 
优: select * from test where a=10 and b>50
差: select * from test where a>50

优: select * from test order by a
差: select * from test order by b
差: select * from test order by c

优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c

优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c

Index principles

1. The fewer indexes, the better

Reason: Mainly when modifying data, the first index must be updated, which reduces the writing speed.

2. Place the narrowest field to the left of the key

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

The above is the detailed content of Sample code sharing for MySQL using joint index function. For more information, please follow other related articles on the PHP Chinese website!