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 the
WHEREclause first. (eg,
WHERE a=12 AND b='xyz' ...)
INcolumn(s); the optimizer may be able to leapfrog through the index.
x BETWEEN 3 AND 9,
name LIKE 'J%') It won't use anything past the first range column.
GROUP BY, in order
ORDER BY, in order. Works only if all are
ASCor all are
DESCor you are using 8.0.
Notes and exceptions:
WHERE, there is no need to go on to
GROUP BY, etc.
ORDER BYcolumn(s), ignoring
DATE(x) = ...cannot use
xin the index.)
text_col(99)) is unlikely to be helpful; may hurt.