--- In firebird-support@yahoogroups.com, "m_formigoni"
<m_formigoni@y...> wrote:

> Hi,
>
> I have a table with the following fields:
>
> IdLog integer not null PK,
> User char(15) not null
>
> Have also 1 index:
> ix_User (User)
>
> I'm trying to do a select where I can get the log records from a
> specific User, or records of all user if '-1' is passed in the

parameter.

>
> I'm using the following select statement:
> select * from LogTable
> where ( (:User = cast('-1' as char(15))) or (User = :User))
>

There may be a few reasons. Perhaps there are a lot of duplicates in
LogTable and the selectivity of ix_user makes a natural scan of more
use.

Firstly, Firebird does not handle this logic well

(SomeNumber = SomeOtherNumber)

ie.

:User = -1

It will evaluate it for every record. If you want proof try this one

select *
from LogTable
where 1=1

It will evaluate "does 1 = 1?" for every record (which is not good).

That is the reason it goes natural though. If it is going to run that
test against every record (because the OR means it must be evaluated
as well), then hitting the index will slow it down.

We used to have the reverse problem, where to ensure 0 records were
returned we would use the condition 1=0 (with the same side effect).
To work around that, we used the equivalient logic to ID = 0 (which
can not happen in our ID domain)

Maybe others can shed some light into some technique for you.

Adam