This article covers when to user mysql multi column index and how does the order of column impact the query and index usage.
Sample table
The following is a sample table which we’ll use for this article.
| ID | FN | LN | Address |
| 1 | FirstName | LastName | Some address |
When does query uses index?
| Index | Query has FN and LN both | Query has only FN | Query has only LN |
| IDX(FN,LN) | Y | Y | |
| IDX(LN,FN) | Y | Y | |
| IDX(FN) | Y | ||
| IDX(LN) | Y |
To cover all types of queries, one option is to create one multiple column index (e.g. FN,LN) and one single column index on FN. This will avoid creation of extra redundant indexes.
Here is a youtube video describing this article.
