An index on a binary field can only ever be useful if the part you're
interested in contains a small porportion of the records. If you ever
update this field or delete records from the table, you should also
add the primary key of the table to the end of the index (i.e. CREATE
INDEX <somename> ON (<BooleanField>, <PrimaryKey>). If not, the server
will have a hard time trying to locate the correct record to update
within the index (it won't affect the select much, just the
update/delete).

Of course, another solution would be to not use the flag at all, but
rather have a trigger inserting records to flag into a separate table
without any other indexes than a primary key. Of course, you'd also
need an UPDATE trigger which can update this table with FLAGGEDRECORDS
if the original record changes and a DELETE trigger. Result:
Modifications to records may take slightly longer, but I'm certain a
SELECT * FROM FLAGGEDRECORDS would execute considerably quicker than
doing a SELECT * FROM <Table> WHERE <IndexedFlag> = '1'.

I have never used external tables, but know they can be useful for
quick imports. I doubt they are equally useful for fast exports, but I
hope some people on this list will tell me that my assumptions are
incorrect.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Edwin A. Epstein, III"
wrote:

> I have a table with 14 million records in it. As part of certain
> operations I need to do against the records I flag them individually
> as part of a que.
>
> I am using a field called DNC_QUED which is a VarChar(1) and ASCII
> character set. I have non-unique index on just the field alone.
> There are no null values and the only values are '1' or '0'.
>
> When I pull a SELECT FIELD1 FROM TABLE1 WHERE DNC_QUED = '1' and
> attempt to fetch all records it takes a very long time (30 minutes
> plus). I checked the plan and it is using the index.
>
> Is there anything I can do to increase the performance of that
> select statement?