Some notes on MySQL index query

Some rules we should remember when doing MySQL index query:

1. Index uses B-TREE, so be sure we understand how B-Tree works.
2. Index is left-most, so if there is (c1, c2, c3) index, then query WHERE c1 = 1 AND c2 = 2 does use index, but not WHERE c2 = 2
3. When considering indexing, remember the following orders:
   1. fields in WHERE is index left-most
   2. then, fields in GROUP BY, ORDER BY
   3. if it is possible, just select fields in the created index so that mysql optimizer does not need to touch the real table data
   4. when there is many fields in WHERE, index exact search (= condition) first, then range (<>, >, <, etc.)
4. In some case, if the range conditions (<>, >, <, etc.) causing temporary table and filesort, try using MySQL Query Optimization using UNION
5. If there are many possible indexes, but mysql optimizer chooses the wrong one when querying, try to use FORCE INDEX