Multiple columns in a single index versus multiple indexes

Question

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?

Answer 1

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.

Answer 2

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/