Hi,

Svein Erling and Russell,

> > On a table with many columns what is recommended (in Firebird)

Recommended if you want speed and that table includes the most fields you
want to access, but personally i prefer normalized schema's. With normalized
schema's you probably end up in more queries that uses JOINs and is mostly
more expensive, but the performance difference isn't necessary huge.

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

Many single fields indices doesn't sound as a good idea to me. Certainly not
on fields which have only a few values. If a statement includes for example
4 fields (and for them are 4 single-field indices) then the engine will
combine indeed these single indices, but that operation can be expensive if
those fields have many duplicates.

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

Indeed!

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

I don't feel myself a oracle here and you have also the knowledge to answer
these questions Svein. You already did :-)

btw, personally i also prefer a PK together with a generator.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81