MySQL Composite Index

Keep the following in mind when creating composite indices for MySQL tables:

• A composite index is by MySQL to optimise queries for columns used in WHERE, ORDER BY, GROUP BY and MIN/MAX.

• A three-column index (col1, col2, col3) can be used for search capabilities on (col1), (col1, col2) and (col1, col2, col3) (ordering doesn't matter). It cannot be used for (col2) or (col2, col3) or (col3). If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows.

• Once you have decided you need a composite index (col1, col2, col3), the preferred ordering is to put the higher cardinality columns first. If column 2 has the highest cardinality, then you may want to create an index (col2, col1, col3) but keep in mind this cannot be used for optimising searches on just (col1), etc.

• MySQL uses only one index per query. Hence, composite indices come in handy. You can use a sub-query to work around this if needed but ideally you should have a composite index with with the relevant columns.

• If you have two separate indices on col1 and col2, MySQL will prefer the one with the highest cardinality. MySQL keeps stats on the cardinality of each index. An index with the most discriminating power allows MySQL to eliminate the maximum number of rows in the quickest time.

• If all the fields selected in a query are covered by an index, MySQL never has to hit the DB and can fetch the fields directly from the index (applies only to InnoDB). So if you select col1 from table1 and col1 is indexed, MySQL will use a covering index and never hit the table.

Reference:

• http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

• http://stackoverflow.com/questions/8213235/mysql-covering-vs-composite-vs-column-index

• http://stackoverflow.com/questions/9537128/is-there-an-optimal-method-for-ordering-a-mysql-composite-index