On 11/8/2010 7:50 AM, Sergio wrote:

>
> My paricular case is a calendar with events in some days. I need to select the pending events. They will be always, lets say, less than 100, while the table, as the the years pass will grow a lot with the old (not pending) events...
>
> So, in my case, makes sense to have an index on the "pending" field (which is smallint and can store 0 or 1)
>

In very early versions of Firebird, an index like that would have
caused enormous problems during garbage collection. Now there's
an algorithm that makes cleaning out old duplicate entries fast
enough.

An index will improve performance when you're looking for the 1%
of the records that are not like the rest, if you can convince
the optimizer to use it. The optimizer may be smart enough to
ignore indexes with terrible selectivity, in which case you may
need to add a plan to force use of the index. But if you
accidentally run a query that looks for the 99% of records that
all have the same value (and the optimizer chooses to use that
index) you'll get poor performance. You can avoid that by
adding a nonsense OR clause - (pending = 0 OR 1 = 2) - that
will cause the optimizer to ignore the index.

Besides, adding and dropping an index is pretty trivial. Try it,
if you like it, keep it, otherwise get rid of it.

Cheers,

Ann