> Using Firebird 2.1.1 for Windows here. I have a this PICKUP table with

two

> indexes:
>
> CREATE INDEX PICKUP_CLIFEC ON PICKUP (CLIENTE, FECHAREC);
> CREATE INDEX PICKUP_FECHAREC ON PICKUP (FECHAREC);
>
> Hint: CLIENTE column is varchar(38), FECHAREC column is date.
>
> Now in a JOIN query that uses the following WHERE clause:
>
> where (A.CLIENTE=:clienteid) and (A.FECHAREC between CURRENT_DATE-31

and

> CURRENT_DATE)
>
> I get this plan:
>
> PLAN SORT (JOIN (A INDEX (PICKUP_FECHAREC), B INDEX (RDB$PRIMARY26), C
> INDEX (RDB$PRIMARY14)))
>
> It's using PICKUP_FECHAREC index (which is based only in the date

column)

> and I think it should be using the PICKUP_CLIFEC index, which starts

with

> the CLIENTE column just like the WHERE clause needs.

It is reasonable to expect that the PICKUP_CLIFEC to be used, but it
depends on the _selectivity_ of the CLIENTE field.

How many distinct Cliente values are there compared to the number of
distinct FECHAREC?


Sean