Create a table:
CREATE TABLE ' Ygzt_test ' (
' ID ' bigint (unsigned) not NULL auto_increment,
' A ' int (one) is not NULL,
' B ' int (one) is not NULL,
' C ' int (one) is not NULL,
' d ' int (one) is not NULL,
PRIMARY KEY (' id '),
KEY ' A ' (' A ', ' B ', ' C ', ' d ')
) Engine=innodb DEFAULT Charset=utf8 collate=utf8_unicode_ci comment= ' test ';
1.Experiment one, no ORDER by
First Add federated Index A,b,c,d
Explain select * from Ygzt_test where a=1 and b=2 and C=3 and d=4
Modify SQL:
Explain select * from Ygzt_test where a>1 and b=2 and C=3 and d=4
Type has been downgraded from ref to index
Modify Index B,c,d,a
Explain select * from Ygzt_test where a>1 and b=2 and C=3 and d=4
Done
2.Experiment two, order by
Building an index, a, b
Explain select * from Ygzt_test where a>0 order by B
As you can see, A>0 uses an index, and order by B is not used
Modify Index to B,a
Explain select * from Ygzt_test where a>0 order by B
Where and order by are not indexed
Thinking of the previous order By+select *
This question is taken out alone in practice:
Modify Index to a
Explain select * FROM Ygzt_test ORDER by a
Explain select * FROM Ygzt_test Force INDEX (a) order by a
Conclusion:
1.--<,<=,=,>,>=,between,like (right blur) applicable index in single-column index
2. The scope of the index, the order of failure, the solution is subject to actual
Query for scopes in MySQL composite index