Indexes on multiple columns in MySQL

When optimizing queries, you don’t need to be reminded that indexes play an important role.

Something to remember though: in real-world examples a multi-column index often outperforms multiple indexes.
So it can be worth changing something like:

INDEX `index1`(`column1`),
INDEX `index2`(`column2`)

to

INDEX `index1`(`column1`, `column2`)

MySQL 5.0 introduced an internal method called “Index Merge” which merges multiple indexes when querying a table (if applicable) but I haven’t seen this being used that often. On the contrarry, MySQL often picks one of the indexes and in these scenario’s it is especcialy usefull to have one multi-column index.

As always: use EXPLAIN to see what can be improved.