--- In ib-support@y..., "csswa" <csswa@y...> wrote:

> This from an MSSQL7 site:
>
> "When you create an index with a composite key, the order of the
> columns in the key is important. Try to order the columns in the key
> as to enhance selectivity, with the most selective columns to the
> leftmost of the key. If you don't due this, and put a non-selective
> column at the first part of the key, you risk having the Query
> Optimizer not use the index at all."
>
> I was under the impression that you order left to right, least
> selective to most selective field -- the opposite of what this guy
> says. I was thinking that least selective first makes for a more
> balanced b-tree. No?

Andrew, I think for IB/FB there is no difference, _index entry_ is
constructed as one integrated value specifying segments values
combination, sequence of fields should be suitable for most common
joins and conditions, no more. Look at test results. I have 2 tables
(Stores and Store_Names) 1:1 containing about 30 000 records. I made 2
indexes on one of them

ST_CODTIP UNIQUE INDEX ON STORES(CODE, TIPSTORE)
ST_TIPCOD UNIQUE INDEX ON STORES(TIPSTORE, CODE)

Code is PK based on generator, TipStore is 1..14.

Create Procedure TestTC
As
Declare Variable TipStore Integer;
Declare Variable Code Integer;
Declare Variable NumReg Char(6);
Begin
For Select First 1000 Code, TipStore From Store_Names
Order By Sname
Into :Code, TipStore
Do Select NumReg From Stores Where Code=:Code And TipStore=:TipStore
Plan (Stores Index (ST_TIPCOD))
Into :NumReg;
End

Create Procedure TestCT
As
Declare Variable TipStore Integer;
Declare Variable Code Integer;
Declare Variable NumReg Char(6);
Begin
For Select First 1000 Code, TipStore From Store_Names
Order By Sname
Into :Code, TipStore
Do Select NumReg From Stores Where Code=:Code And TipStore=:TipStore
Plan (Stores Index (ST_CODTIP))
Into :NumReg;
End

in both procedures loop is organized on one table to make indexed
fields values 'random' via Order By Name and records from second table
are retrieved using different indexes. To get statistically correct
result loop is made 1000 times. Result:

execute procedure TestCT
Elapsed time= 0.15 sec

execute procedure TestTC
Elapsed time= 0.15 sec

Best regards, Alexander V.Nevsky.