At 04:13 PM 8/03/2004 +0200, you wrote:

>I keep on having this argument with everyone and it seems there are as many
>documented proofs as there are people.
>
>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

With a reservation the "many", single-column indexes will help on columns
that are searched with OR conditions and they will be used for AND
searches. The trap with "many" is that there is extra overhead indexing
columns that are never searched. There is also likely detriment in
indexing columns that have low selectivity, because queries will find the
sets faster by not using an index.

>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.

Yes, for ordered searches and for sorts, provided the compound indexes are
in the same order as the search or ordering criteria. Otherwise, such
indexes will only expose the leftmost column of the index - so you have
extra payload on such indexes, with minimal payback.

>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.

It's true of FB as well. It much depends on what you mean by
"inserts". With huge batches, there are multiple benefits from setting
indexes inactive until the operation completes. For interactive positioned
inserts the extra cost is incidental and doesn't justify crippling the SELECTs.

>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. <-- that's the crux of it.
>What is the general feeling in this respect?

I agree with your preference 100%. I avoid like the plague using compound
PKs if there are going to be dependents. The other aspect of compound keys
is the incidence of "meaningful data" in the segments. For me, it might be
justified for a static lookup table that I'm not going use declarative RI
on, but otherwise never.

/heLen