3

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

closed as unclear what you're asking by Philᵀᴹ, Paul White says GoFundMonica, ypercubeᵀᴹ, Shanky, dezso Feb 20 '15 at 9:26

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Come on guys. The question is clear. It is a commonly asked question. The novice does not understand that that is the question to ask. Plus several good answers were posted before you "closed" it. – Rick James Mar 18 '15 at 0:13
5

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

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.

2

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.

0

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.

0

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.

  • "multiple single column indexes will never be used in a single query": Not true. In rare cases, MySQL will use multiple indexes. – ypercubeᵀᴹ Feb 20 '15 at 5:53
  • @ypercube, thanks for pointing that out. could you please mention a scenario ? – Akshat Feb 20 '15 at 6:09
  • See the links in Rolando's answer about index-merge algorithms. – ypercubeᵀᴹ Feb 20 '15 at 6:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.