Note
Firebird v5.0 introduced partial indices that could be used to solve this problem.
Marius Labuschagne wrote:
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.
Pavel Cisar answers:
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).