Hi Rod,

> >From the good old days of Paradox we still have a lot of Composite
> indices on our relations. (now using FB1.0.3 and IBO 4).
>
> These were useful for setrange / findkey etc. Now that we have got
> rid of this sort of code and are using queries - I have a few
> questions.
>
> 1) The optimizer will ofter choose a composite index over a single
> column index - e.d index(accnum) vs index(accnum,date). Is this a
> problem, or in firebird has the index just build it's info on the
> first column anyway?

The Firebird 1.5 optimizer does a better job in choosing the right
indices to use.

> (I will remove the composite indexes in time- but there may be some
> legacy code hiding somewhere)
>
> 2) Is splitting a composite index into separate indices better ?

Yes, as Firebird can combine indices. Mind you, low-selectivity
indices have no use.

> 3) Is a FK constraint on a join table a better index than creating an
> index on the join table ? (In terms of performance - not integrity)

A FK constraint will automatically add an index to the columns in
the "child" table.

> 4) the RDB$Selectivity column - I read a value of 1 or greater means
> the index is inefficient - true / false - more info required ?

More info required I believe.

You might want to read the help that Craig Stuntz provides with his
PLANalyzer tool:
http://delphi.weblogs.com/IBPLANalyzer

It's written for InterBase, but, of course, applies to Firebird as well.

With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com