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

About NhocConan

A super lazy guy who tries to write tech blog entries in English.He is lazy, so he can only write when he is in a good mood or when he is tired of coding.

Leave a comment

Your email address will not be published. Required fields are marked *