Building a composite index

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 WHERE clause first. (eg, INDEX(a,b,...) for WHERE a=12 AND b='xyz' ...)
• IN column(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 are ASC or all are DESC or 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 to GROUP BY, etc.
• There are cases where it is useful to index only the ORDER BY column(s), ignoring WHERE.
• Don’t “hide” a column in a function (eg DATE(x) = ... cannot use x in the index.)
• ‘Prefix’ indexing (eg, text_col(99)) is unlikely to be helpful; may hurt.

More details and tips .