Performance comparison of MySQL composite index

Let's take a look at some test examples

The code is as follows	Copy Code
SELECT * FROM Dlog_user-online_status, username

First look at the inline SQL statement above, username is the primary key of the Dlog_user table, Dlog_friend has a composite primary key combined by username and Friend_username.

Test conditions One:

Dlog_user and dlog_friend two tables in addition to the primary key does not have any indexes, the result of this SQL statement explain is dlog_user do a full table query (Type=all), extra information is use Filesort

Test Condition Two:

Dlog_user Add composite Index

The code is as follows	Copy Code
Create INDEX Idx_online_status on Dlog_user (username, online_status);

Explain the SQL statement again, the full table query, and the use Filesort

Test Condition Three:

Modify the composite index to change the order of username and online_status, and the result is: Type=index, extra= null

The index is working!

Test condition Four:

Change the SQL statement as follows:

The code is as follows	Copy Code
Select A.* from Dlog_user a inner join Dlog_friend B in a.username=b.friend_username where b.username= ' ld ' ORDER by A.onli Ne_status Desc,a.username

That is, when ordered by, two fields are sorted in the opposite way, and no matter how the index is adjusted, executing this SQL statement is a full table query and user Filesort.

Conclusion:

1. The fields in order by must be indexed according to the sequence in the SQL statement;
2. The sort order of the fields in the orders by must always be the same, otherwise the index is invalid.
3. Indexing is not necessarily effective, check with the actual SQL.

Here are a few examples to compare the performance impact of different query conditions.

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 Poor: SELECT * from Test where A50 Excellent: SELECT * from Test where ORDER by a Poor: SELECT * from test where ORDER by B Poor: SELECT * from test where ORDER by C Excellent: SELECT * from Test where a=10 order by a Excellent: SELECT * from Test where a=10 order by B Poor: SELECT * from Test where a=10 order by C Excellent: SELECT * from Test where a>10 order by a Poor: SELECT * from Test where a>10 order by B Poor: SELECT * from Test where a>10 order by C Excellent: SELECT * from Test where a=10 and b=10 order by a 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 a Excellent: SELECT * from Test where a=10 and b>10 order by B Poor: SELECT * from Test where a=10 and b>10 order by C

Indexing principle

1. The less the index the better
Reason: mainly in the modification of data, the first index should be updated to reduce the write speed.
2. The narrowest field is placed on the left side of the key
3. Avoid file sort sorting, temporary tables and table scans.