MySQL indexes: having multiple indexes or having one multipolar key?

Question

When I manually create tables in MySQL, I add indexes, one for each field, which I think I will use for queries.

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

What's the difference? Better than the other? In this case?

Thanks!

Answer 1

It depends on your requests. In some queries it is better to use multi-column indexes, some not. EXPLAIN is your friend.

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

Also a very good resource:

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

Answer 2

Also not a good strategy, but if I had to choose, I would choose several single indexes.

The reason is that an index can only be used if you use all fields in any full index prefix. If you have an index (a, b, c, d, e, f) then this is great for a query that filters on a or a query that filters on a and b , but it will be useless to filter queries only on c .

There is no simple rule that always works to select the best indexes. You need to look at the types of queries you make and select indexes that speed up those specific queries. If you are careful about the order of the columns, you may find a small number of indexes that will be useful for several different queries. For example, if you filter both a and b in one query and you filter only b another query, then the index on (b, a) will be used for both queries, but the index an (a, b) will not.