MySQL Guidelines for building indexes

Several principles of index building

1. The leftmost prefix matching principle, very important principle, MySQL will always match right until it encounters a range query (>, <, between, like) to stop matching, such as a 1= "" and= "" b= "2" c= "" > 3 and D = 4 if established (A, B , c,d) The index of the order, D is not indexed, if the establishment (A,B,D,C) of the index can be used, a,b,d order can be arbitrarily adjusted.
2.= and in can be disorderly, such as a = 1 and B = 2 and c = 3 build (a,b,c) index can be arbitrary order, the MySQL query optimizer will help you to optimize the form of the index can be recognized
3. Try to select a high-differentiated column as the index, the formula for the degree of sensitivity is count (distinct col)/count (*), indicating the scale of the field is not repeated, the larger the proportion of the number of records we scan, the difference between the unique key is 1, and some states, sex fields may be in front of the big data to differentiate is 0, then one might ask, what is the empirical value of this ratio? Using different scenarios, this value is also difficult to determine, generally need to join the field we are required to be more than 0.1, that is, the average 1 scan 10 records
4. The index column cannot participate in the calculation, keep the column "clean", such as from_unixtime (create_time) = ' 2014-05-29 ' can not be used to the index, the reason is simple, B + tree is stored in the Data table field values, but when the retrieval, You need to apply all the elements to the function to compare, obviously the cost is too large. So the statement should be written create_time = Unix_timestamp (' 2014-05-29 ');
5. Expand the index as much as possible and do not create a new index. For example, the table already has an index of a, now to add (A, b) of the index, then only need to modify the original index

MySQL Guidelines for building indexes