At 12:48 13.01.2003 +0100, you wrote:

>In message <5.1.0.14.0.20030113110849.02494ab0@[158.36.132.22]>, Svein
>Erling Tysvaer <svein.erling.tysvaer@...> writes
> >The number of indexes is not all too important, what matters is their
> >selectivity. Just make sure you do not create indexes on fields for which
> >many records hold the same values.
>
>Is there some sort of way of figuring out what the 'boundary' is?
>
>Like, a spread of 50% ( a boolean field ) is obviously silly. But what
>sort of percentage should I drop to, before actually creating an index?

Well, I don't know! Rather than telling people not to create an index, I
normally tell them to append the primary key to the end of the index - e.g
use CREATE INDEX IDX_COUNTRY ON CUSTOMER(COUNTRY, PK) rather than just
CREATE INDEX IDX_COUNTRY ON CUSTOMER(COUNTRY). This is because the latter
will have lots of duplicates making deletes/updates slow, whereas the first
is unique.

And it isn't just a simple case of a percentage. Sometimes the optimizer
can be tempted to use too many indexes. E.g. running a query like SELECT
NAME FROM CUSTOMER WHERE COUNTRY = 'DENMARK' may or may not benefit from
using the index above. However consider
SELECT NAME FROM CUSTOMER WHERE COUNTRY = 'DENMARK' AND
SOSIAL_SECURITY_NUMBER = '315134515'. This would undoubtedly benefit
enormously from an index on SOCIAL_SECURITY_NUMBER and the ideal plan would be
PLAN(CUSTOMER INDEX(IDX_SOCIAL_SECURITY_NUMBER). The optimizer, however,
may choose
PLAN(CUSTOMER INDEX(IDX_COUNTRY, IDX_SOCIAL_SECURITY_NUMBER) which would be
slower (I do not know the impact in this simple case, but it does create
problems as soon as you introduce joins).

And then there are some cases where you need the server to be responsive,
whereas other queries may run for minutes without anyone complaining (e.g.
online registration must complete quicker than annual reports).

Hence, you have to examine your data as well as the queries you normally
run against them to decide whether an index is benefitial or not in that
particular case.

HTH,

Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation