MySQL: Difference between `... ADD INDEX(a); ... ADD INDEX(b);` and `... ADD INDEX(a,b);`?

Question

Can someone tell me what is the difference between these two:

ALTER TABLE x1 ADD INDEX(a);
ALTER TABLE x1 ADD INDEX(b);

AND

ALTER TABLE x1 ADD INDEX(a,b);

I know this goes down to the very basics but sometimes I find the former seems to be a little faster than the latter. Is it just my feeling or is there some actual reason for it?

Answer 1

With your second option, the following query will not use the index:

SELECT * FROM x1 WHERE b = 'something';

The order in which columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.

Source: Wikipedia - Database Index: Column Ordering

Answer 2

INDEX(a,b) will speed up searches for a or a and b, but not b alone, whereas the former (INDEX(a)... INDEX(b)) will work in all cases.

Answer 3

The combined INDEX is a combination of the keys "a" and "b". It improves access significantly if either "a" or "a" AND "b" are part of the search expression.

This index is not helpful if you provide only "b" in your SQL statements.

Therefore it might be useful to provide two different indices - but they should use different names.

Depending on the access patterns i would recommend an index on "a" and "b" and an additional index on "b" if this matches your needs.

Please keep in mind, that any additional index slows down the database on all operations that modify data. Some times it is better to keep some indices away. It is normally a good advice to NOT USE indices on any column of a table.

An one more hint: to decde whether an INDEX(a,b) or INDEX(b,a) should be used, have a look at the distribution of your data. Put the values with the higher spread of different values into the first column of the index to increase the selectivity of that index. This value is normally based on the quality of the first index element.

For example an index on columns NAME and SEX should be created as INDEX(NAME, SEX) because there are many more names that different sex(es ?).