Great demo, Alexander :-) thanks. One thing though: are you sure the
server used the plans you specified and didn't just use the same plan
for both?

Regards,
Andrew Ferguson
-- Wild horses did in fact drag me away.

--- In ib-support@y..., "alex_vnru" <ded@h...> wrote:
> --- 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.