MySQL Mysql Performance Tips Building a composite index
Example
In many situations, a composite index performs better than an index with a single column. To build an optimal composite index, populate it with columns in this order.
=column(s) from theWHEREclause first. (eg,INDEX(a,b,...)forWHERE a=12 AND b='xyz' ...)INcolumn(s); the optimizer may be able to leapfrog through the index.- One "range" (eg
x BETWEEN 3 AND 9,name LIKE 'J%') It won't use anything past the first range column. - All the columns in
GROUP BY, in order - All the columns in
ORDER BY, in order. Works only if all areASCor all areDESCor you are using 8.0.
Notes and exceptions:
- Don't duplicate any columns.
- Skip over any cases that don't apply.
- If you don't use all the columns of
WHERE, there is no need to go on toGROUP BY, etc. - There are cases where it is useful to index only the
ORDER BYcolumn(s), ignoringWHERE. - Don't "hide" a column in a function (eg
DATE(x) = ...cannot usexin the index.) - 'Prefix' indexing (eg,
text_col(99)) is unlikely to be helpful; may hurt.
