MySQL indexing and how to use it correctly

I. Type of index

B-Tree indexes: Most of them are, so the characteristics of B-trees limit how indexes are used; you must look at the correct usage limits for indexes (with the limitations of composite indexes)http://blog.csdn.net/lovemdx/article/details/17683647

Hash index: only memory engine support

Correct use of index of B-Tree

1. Select D from table where A = "x" and b= "Y" and C = "Z": Index is available for A B C at this time
2. Select D from table where A = "x" and B >= "Y" and C = "Z": At this point an index can be used for A, the index can be used for B, and C cannot use the index. is because if a field in the Federated Index uses a range-of-lasso query, the subsequent fields will no longer use the index
3. The Federated index ABC can only meet A,ab, ABC three queries, if need B, c separate indexes need to be indexed separately. With explain's key_len, you can see which parts of the federated index are used. The AC query can only use part A of the federated index
4. The B-Tree itself is ordered, and the order-by field is indexed in the where and the results are no longer sorted . Detailed http://www.cnblogs.com/zhaoyl/archive/2012/05/04/2483513.html
5. In addition, ORDER by A DESC, B ASC can not use the index, the use of the index must be consistent with the AB order . That is, if there is such a requirement, it is recommended that the processing be consistent in order A and B, for example, the value of B is negative.

MySQL indexing and how to use it correctly