At 05:28 PM 17/07/2004 +0000, you wrote:

>The two other approaches I am considering, if the speed is not
>acceptable are:
>
>1. Add a column with a unique index and triggers that will assign
>random numbers to that column in two distinct ranges controlled by
>whether the record is "Open" or "Closed". That should speed the query
>considerably.
>
>2. Add a single column table populated with just the key fields of
>"Open" records. Again controlled by triggers on the main table. Join
>this table to the query. In effect, rolling my own index.
>
>With your deep experience of Firebird, do you have any feel for which
>might be faster and/or more maintainable?

Neither.

The solution for a poorly-selective index is to raise its selectivity. You
do this by creating a composite index across the two-value column and a
highly-selective column (such as the primary key). Ideally the PK is an
integer or BigInt:

create index ixYesNo on aTable(TrueFalse, PriKey)

Firebird will use the index for the search on TrueFalse because TrueFalse
is the leftmost column in the index. The higher selectivity avoids the
problem of long duplicate chains forming in the index tree -- the commonest
cause of slow queries on Boolean conditions.

Another thing to watch out for with standard SQL and these "logical"
columns is that, unless you specify a default, you have implemented a
three-phase Boolean, not two. "Not True" and "Not False" are both null
unless specifically set to "False" or "True", respectively. Null doesn't
mean "false", it just means "unknown". Also, null is never evaluated as
"equal to" anything, including another null.

I guess that, if you've converted a Paradox database and haven't had time
to fix the keys, you'll still have hierarchical key structures. You'll be
aware, no doubt, of what a performance killer hierarchical keys can be for
an SQL database...works wonders with the Paradox QBE engine (which the
BDE's Paradox driver resolves all SQL statements to) but it's ghastly for
Fb/IB.

Helen