MySQL Indexes: Having multiple indexes vs. having one multi-field key?

Question

When I manually create tables in MySQL, I add indexes one at a time for each field that I think I will use for queries.

When I use phpMyAdmin to create tables for me, and I select my indexes in the create-table form, I see that phpMyAdmin combines my indexes into 1 (plus my primary).

What's the difference? Is one better than the other? In which case?

Thanks!

Answer 1

This actually depends on your queries. Some queries make better use of multicolumn indexes, some not. EXPLAIN is your friend.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Also a very good resource is here:

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

Answer 2

Neither is a particularly good strategy, but if I had to choose I'd pick the multiple single indexes.

The reason is that an index can only be used if you use all the fields in any complete prefix of the index. If you have an index (a, b, c, d, e, f) then this works fine for a query that filters on a or a query that filter on both a and b, but it will be useless for a query filtering only on c.

There's no easy rule that always works for choosing the best indexes. You need to look at the types of queries you are making and choose the indexes that would speed up those particular queries. If you think carefully about the order of the columns you can find a small number of indexes that will be useful for multiple different queries. For example if in one query you filter on both a and b, and another query you filter on only b then an index on (b, a) will be usable by both queries but an index an (a, b) will not.