I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?

SHORT ANSWER

Only as a last resort

LONG ANSWER

Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer.

I have written about this before

• Sep 18, 2012 : How are multiple indexes used in a query by MySQL?
• Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2)

In essence, MySQL will do lookups along multiple indexes and perform an index merge of the results. This would only be done as a last resort if there are no compound indexes that are usable for your query’s WHERE, GROUP BY, and ORDER BY clauses.

The order of the columns in the multiple-column index also has a bearing. If you have the following:

• Table with indexes (a),(b),(c)
• Same table with index (a,b,c)

The three-column index would be better if the WHERE, GROUP BY, and ORDER BY clauses harness the indexed columns in the order inside the index (a,b,c). Anything different combination could possibly result in an index range scan of (a,b,c). Next best choice could be full index scan of (a,b,c) . If these are not possible, the last resort becomes the merge of searching (a),(b),(c) separately.

If you are testing a and b, INDEX(a, b) is likely to be better.

Indexing a flag (by itself) is almost never useful.

Please provide SHOW CREATE TABLE and a few WHERE clauses; I will give specific advice.

Here’s a quick cookbook for building an INDEX that will often be optimal. Given a WHERE with a bunch of expressions connected by AND:

1. List all the columns (if any), in any order, that are compared to a constant and not hidden in a function.

2. You get one more chance to add to the index; do the first of these that applies:

2a. A column used in a ‘range’ — eg, BETWEEN, LIKE without leading wildcard, ‘>’, etc.

2b. All the columns, in order, of the GROUP BY.

2c. All the columns, in order, of the ORDER BY if there is no mixing of ASC and DESC.

There are exceptions where this simplified cookbook fails, but I think it covers a large percentage of cases. Two notable exceptions are “covering indexes” and OR clauses. And it does not admonish you not to add redundant indexes.

There is no clear answer to your question, as it really depends on the query. BUT: If you like to filter e.g. for two colums a combined index will have a better effect.

When a query plan is constructed, MySQL finally decides to use only one of the indexes on the table which means that multiple single column indexes will never be used in a single query. However, if you have multi-column single index which covers all the required fields (coverage index), the database will never have to read from the disc which will make the query very fast

table:   ID|Name|age|sex
query:  select sex from table where name= 'x' and age ='y';   

in this query, if you have 3 seperate indexes on name, age and sex only the most useful one(calculated by the optimizer) will be used whereas if you have a single index on name-age-sex, it will be much fast.

It is also very important to note that the indexes are evaluated from left to right in the query, that is, if you have an index on age-name-sex, it will not be used as name is checked before age in the query.