Hello...,

Background: It has been established that indexing a boolean (yes/no,
1/0) column is not recommended in Firebird for performance reasons (i.e.
update/insert speed, confusing the optimizer).

My situation: I have a large table with a 'status' column. This table
has an INTEGER primary key fed from a generator. The 'status' column is
also an integer field, but is limited to five values ([1..5]) at the
moment. The distribution of these five values are not even, with the
value '1' expected in more than 60% of the rows. The rest of the values
are divided more or less evenly.

At some point in the near future i'll need to write some
queries/storedproc that will only process records of a certain 'status'
value. Without an index, this operation will normally require a full
table scan. This makes me a bit nervous.

Will it be a good idea to either :
a. create an index on the 'status' column ?
b. create a compound index on 'status' + 'pk' columns ? (supposedly this
will remove the 'long index chain' caused by [a] above. In a cursory
test with a small dataset, "select * from table where status=1" does
indeed use the index.). Is this the best thing to do?
c. split the table into five different tables with identical structure,
and remove the 'status' column altogether ? (I don't like the idea at
all, but someone suggested this, so i'll ask for second opinions).

Any inputs are appreciated.
Thank you very much in advance,
sugi.