Marius Labuschagne napsal(a):

> Hello,
>
> Can someone please tell me if I should build an index on a field when
> the the values for the field is always a boolean value (Yes or No).
>
> I execute a lot of queries against this table based on whether the field
> is 'No'. Everyday approximately 1000 new rows of data gets stored into
> this table where the field will be 'No'. The table has millions of
> records present where the value has been set to 'Yes', and they cannot
> be removed.

Index on columns like this is incredibly dense, so it's often very
shallow (which is a good thing) and it's significantly smaller (also
good thing). This means that its processing overhead on selects is lower
than on average index. However, it has higher than average overhead for
maintenance, especially when keys are updated or deleted (problem is not
in update itself, but in subsequent garbage collection). In your case
when data are mostly permanent, the maintenance overhead could be
neglected. In your case, when there is a significant imbalance in key
distribution, you would get significant performance improvement for
queries that filter rows with less common key value, and you may suffer
performance hit for queries for complementary queries (at least until
data distribution statistics would be implemented in Firebird). However,
these indices have very poor general selectivity value, so Firebird
optimizer may decide to not use the index even if it would improve
performance, so you always need to check the query plan to verify
whether such index isn't useless.

To sum it up, there is no general rule that would recommend or not to
use such indices. The actual usefulness of such index depends on type of
queries you mostly use, and physical data storage characteristics that
affect optimizer decisions. The best approach to evaluate its usefulness
is to try it with your real data and queries (and dot's forget to check
the execution plans).

best regards
Pavel Cisar
IBPhoenix