MySQL 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 .