Sorry Dean,
Although I think you're right in what you say for many databases, Firebird can actually use indexes for both COLA and COLB in your example (although there may be cases where it would choose to use only one of them).

I'm curious to learn the answer to Sergios question,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Dean Harding
Sent: 19. september 2008 08:32
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] using indices

Sergio H. Gonzalez wrote:

> Why FB does not uses both indices?

Because it can't. Indexes are not magic, and for me, the easiest way to
think of an index is as a pre-sorted list of pointers to the actual rows
(sorted by the columns of the index).

Let's do a quick example. Say you've got two columns like this:

Row# COLA COLB
1 A G
2 A B
3 B F
4 B A
5 C D
6 A C
7 B E
8 C H

And a separate index on each, the indexes will be stored somewhat like this:

IX_COLA A->1, A->4, A->6, B->3, B->4, B->7, C->5, C->8
IX_COLB A->4, B->2, C->6, D->5, E->7, F->3, G->1, H->8

So you can see the database can easily walk through IX_COLA in order, or
it can walk through IX_COLB in order, but it can't walk through both in
order.

If you have "SELECT * FROM TBL WHERE COLB > B" it can walk the index on
COLB to select out C, D, E, F, G and H values quickly.

However, if you had "SELECT * FROM TBL WHERE COLA=A AND COLB > B", it
can use the index on COLA to find the three "A"s and then it's got to
look at the row data directly to see whether COLB > b.

Hope that helps!

Dean.