Ann W. Harrison wrote:

>
> At 06:07 PM 9/30/2002 +0200, Martijn Tonies wrote:
>
> >Question: I tend to put the _most_ selective first to reduce index page
> >reads and narrowing down any results from the index...
> >Isn't this right?
>
> Nope. Firebird treats the whole index key as if it were a single
> string of binary bytes. The only thing that affects the number of
> index reads is the compressed length of the key. Leading duplicate
> bytes disappear. ...
> ...

I've just "stolen" the following comment in a message today on b.p.i.sql:
------------------------------
<<
Experiences from my current project:

If you use two columns to combine them in a compound index be careful if
the first column has a very bad selectivity (many duplicate values).

We all know that if you use a non compound index which has many dups on a
specific value the lookup on one of those rows is getting slower the more
dups you have.
But this is also applicable for one column of a compound index.

I our current project I could increase the speed by about 500 times for
retrieving rows by doing the following:
I defined an additional field which contains the values of the both columns
in question stripped together by a trigger and defined a no compound index
on that single column instead of the compound index on the both columns.

>>

------------------------------

Doesn't this indicate that Martijn's way of defining multicolumn indexes is
a good one?


Regards,
Aage J.