>Can anyone explain why FB uses all 3 in the OR query yet only 2 in the AND query (See definitions below)?

I'll use a different example, not necessarily restricted to databases. Let's say you have data of the worlds' population containing the following information:

PIN
CountryOfResidence
MemberOfFirebirdFoundation

Now, suppose you wanted to find all Norwegians that are members of the Firebird Foundation. I'd say walking through the members list and see which of us came from Norway would be quicker than combining the members list and the CountryOfResidence list and see where you found matches. So for this AND, I'd prefer just to use one index.

Suppose you (for some unexplainable reason) want to find all that are either Norwegians or members of the Firebird Foundation. The members list contains only a (small) subset of the required result set, so the solution above is no longer viable. The choice is either to walk through the entire worlds population (bad idea) or combine the two lists. So for this OR, I'd prefer to use two indexes.

Hence, AND and OR are completely different questions and the best way to achieve the result are normally very different.

HTH,
Set