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:
|
1 2 |
INDEX <code>index1</code>(<code>column1</code>),
INDEX <code>index2</code>(<code>column2</code>) |
to
|
1 |
INDEX <code>index1</code>(<code>column1</code>, <code>column2</code>) |
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.
Tags: mysql
