Performance Comparison of MySQL composite indexes

Composite index is a common data Optimization Method in mysql. Next I will introduce the performance comparison of composite indexes in mysql.

Let's look at some test instances.

The Code is as follows:	Copy code
Select * from dlog_user order by online_status, username

First look at the preceding inline SQL statement. username is the primary key of the dlog_user table. dlog_friend has a composite primary key consisting of username and friend_username.

Test Condition 1:

Dlog_user and dlog_friend tables do not have any indexes except the primary key. The result of this SQL statement EXPLAIN is that dlog_user performs a full table query (type = ALL), and the Extra information is use filesort.

Test condition 2:

Add a composite index for dlog_user

The Code is as follows:	Copy code
Create index idx_online_status on dlog_user (username, online_status );

EXPLAIN the SQL statement again, or full table query and use filesort

Test Condition 3:

Modify the composite index and change the order of username and online_status. The result is: type = index, Extra = NULL.

The index takes effect!

Test Condition 4:

Modify the SQL statement as follows:

The Code is as follows:	Copy code
Select a. * from dlog_user a inner join dlog_friend B on a. username = B. friend_username where B. username = 'ld 'order by a. online_status desc, a. username

That is, when order by is executed, the two fields are sorted in the opposite way. No matter how the index is adjusted, full table query and user filesort are required to execute this SQL statement.

Conclusion:

1. The fields in order by must be indexed in the SQL statement ORDER;
2. The ORDER of the fields in order by must be the same; otherwise, the index is invalid.
3. index creation is not necessarily effective. Use the actual SQL statement to check it.

The following example compares the impact of different query conditions on performance.

Create table test (a int, B int, c int, KEY a (a, B, c ));

The Code is as follows:

Copy code

Excellent: select * from test where a = 10 and B> 50 Difference: select * from test where a50 Excellent: select * from test where order by Difference: select * from test where order by B Difference: select * from test where 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.