--- In firebird-support@yahoogroups.com, "Russell Eva" wrote:

> I keep on having this argument with everyone and it seems there are
> as many documented proofs as there are people.

Not quite, I'll just give you my opinion and have no proof to back it
up.

> On a table with many columns what is recommended (in Firebird)
> Many simple indexes (ie one field per index) to allow the RDBMS to
> work it out for itself

That's how we tend to do things, occasionally adding the PK to
increase selectivity (good for updates/deletes).

> Or
> Few complex indexes based on the majority of the querys fired
> against the table, taking into consideration that a query where
> clause interrogating columns a, b, and c may use an index
> comprising a, b, c, and d.

If your fields are not very selective (though better than no index at
all), then I guess this could be a way to deal with it. Though note
that the order of the fields within the index is of importance and
could greatly effect your query execution time (even in the case you
mention, it would not be irrelevant whether the index was on (abcd),
(acbd) or (bcad)).

> All documentation that I have read advise against indexes on columns
> that have minimal values (eg active_flag (y/n) etc), and in my
> experience the more indexes on a table, the worse the performance of
> inserts etc. I do realize that this varies from DB to DB, but at
> this stage I am only interested in FB.

Occasionally even indexes on Boolean columns may be useful, even
though this is only valid if you are interested in the rare cases with
one value where the alternative holds lots of records (e.g. a hotel
booking system could have a field for 'President/King' and index this
Boolean field, simply because it would be interesting to know whether
such a person had booked a room). For such cases though, always add
the PK to the index.

> My preference is to have a simple pk (ie a generator) (this also
> keeps fk's simple) and then Complex indexes based on table use.
> What is the general feeling in this respect?

My 'general feeling' is that you should wait for Arno to answer your
question (he is the oracle in this regard). But I also tend to use
generators for pks even though I generally avoid complex indexes.

Set