Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE clause? For example....

index1('col1')
index2('col2')
index3('col1','col2')

select some_column from table where col1 = this;                    //uses index1
select some_column from table where col2 = that;                    //uses index2
select some_column from table where col1 = this and col2 = that;    //uses index3

Explain... seems to indicate it chooses an index with the least number of pertinent columns.

Thanks.

The query

select col1,col2
from mytable
where 
  col1 between a1 and b1
  and col2 between a2 and b2

can be answered using index3 without accessing the table.

If there is only index1 and index2 for example the following methods are possible:

• method I:

1) check for each row from mytable if col1 between a1 and b1 and col2 between a2 and b2

• method II:

1) find all rows with col1 between a1 and b1 by using index1

2) lookup each row in mytable and check if the col2 between a2 and b2

• method III:

1) find all rows with col1 between a1 and b1 by using index1

2) find all rows with col2 between a2 and b2 by using index2

3) find the intersection of the rows found by step 1 and step2

All of these methods are inferior to using index3:

Method I needs to scan the whole table even if there are only a view (or no) rows that satisfy the condition.

Method II can be even worse than method I if step 1 returns a lot of possible candidates that must be looked up.

In Method III both step 1 and step 2 can return a lot of rows but the intersections is only a small number of rows.

StackExchange Database Administrators Q#63703, answer score: 2