Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Imagine a table where there are separate indexes on columns a and b.

The query runs somewhat like this:

SELECT col1, col2 FROM `table` WHERE a = 'value_1 AND b = 'value_2';

Now, does this query always use the indexes on both the columns? If so, what is the mechanism? And if not, is there any condition which MySQL checks to decide which index to use when more than one are available?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

MySQL is capable of doing an INDEX MERGE which allows MySQL to search on both indexes and merge the results. Of course, the MySQL Query Optimizer is the arbitrator of this process.

From my perspective, just looking at your query and its WHERE clause, I would create a compound index on your table and an additional index. Which compound index, (a,b) or (b,a) ?

I would next check the cardinality of each column as follows:

SELECT COUNT(DISTINCT a) a_count FROM `table`;
SELECT COUNT(DISTINCT b) b_count FROM `table`;

Based on these counts

  • If a_count < b_count, then I would create compound index (a,b) and an index on b
  • If a_count > b_count, then I would create compound index (b,a) and an index on a
  • If a_count = b_count, then I would create one pair or other

For more information, please read the following links:

share|improve this answer
    
Thanks. One question, cardinality of column is important in deciding whether to apply index on a column. Can you please explain why? And why would cardinality play a role in deciding the ordering of the columns here? If you can update your answer with a detailed explanation, I would be really grateful. –  Cupidvogel Sep 18 '12 at 16:45
    
Personally, I like using lower cardinality columns in the front of a compound index in the event I expect to do range queries. You could make things interesting by just creating two compound indexes (a,b) and (b,a) and let the Query Optimizer decide which is best. I prefer anticipating the data, index column cardinalities, and creating indexes that match WHERE, GROUP BY, and ORDER BY clauses. Knowing what queries you will be running helps you proper plan the indexes you be making. –  RolandoMySQLDBA Sep 18 '12 at 17:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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