Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

The short version of my question is what's the difference between three indexes each indexing a single column and one index indexing three columns. Background follows.

I'm primarily a programmer but have to do DBA work because we don't have a DBA. I'm evaluating our indexes versus the queries run against a particular table. The table as 3 columns that I'm often filtering against or getting the max value of. Most of the time the queries look like

select max(col_a) from table where col_b = 'avalue'

or

select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'

All columns are independently indexed. My question is would I see any difference if I had an index that indexed col_b and col_a together since they can appear in a where clause together?

share|improve this question
1  
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure... –  Kyle Brandt Apr 30 '10 at 17:53
    
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/… –  Tim Coker Apr 30 '10 at 17:57

2 Answers 2

up vote 2 down vote accepted

For simple examples for questions like these, it's easy to tell which is better. For real-world circumstances, your mileage may vary significantly based on other parts of the query.

In MySQL, and perhaps other databases, you could create an index on (col_b, col_a) and use it for both of your queries - any left-most portion of the multi-column index is itself an index. An index on (col1, col2, col3) is also an index on (col1) and (col1, col2).

To answer your specific question, I think you would derive significant benefit from indexing col_b and col_a together. If you stick with just the independent indexes for each column, one of two things happens - either the database uses one of the indexes to consider a subset of rows that match one of the values and then scans them for the second value, or it does some fancy on-the-fly index merging to emulate an index on both columns. MySQL 4 would do the former, and MySQL can do the latter. In either of those cases, you might as well create the combined index, because you expect to use it.

share|improve this answer
    
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index. –  Tim Coker Apr 30 '10 at 19:21
    
That is pretty much the case everywhere. –  Peter Eisentraut Apr 30 '10 at 23:18

In MySQL, for most cases one single index is faster. You can see some benchmarks here:

http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

share|improve this answer
    
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much. –  Tim Coker Apr 30 '10 at 18:27

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.