At 03:29 PM 23/04/2007, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
>wrote:
> >
> > This would be a good place to start:
> > http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert1
> >
>
>Dear Helen,
>
>Thanks very much for the link. It's very informative. The way that FB
>handles index optimization means that my compound key of A, B and C
>would not help FB very much because it will need to build a bitmap
>for each of the condition for A, B, and C alone. So in my case,
>there are around 1 million records for the condition (A>1) and the
>same for (B>2) and (C>3). So it basically has to effectively scan
>through the 1 million records to build bitmaps and "and" the bitmaps,
>before knowing which 10 records are the first. Is my understanding
>correct?

No. To know which 10 records are the first, it has to create the
set, order it, and then take the first 10 from that ordered
set. Your performance problems are not happening there. They are
happening on the search (the WHERE part), which precedes the sorting
and slicing.

>That doesn't sound very clever. If I have a unique compound key of
>(A, B) and I want to locate a particular record, I would normally use
>something like SELECT * FROM mytable WHERE A = 1 AND B = 2. Now if
>there are 1 million records satisfying A=1 and another million
>records satisfying B=2, then will FB need to read 2 million records
>before finding the intersaction?

It depends, but not usually. It would use indexes if there were
useful ones available. However, an equality search on both fields of
a two-field index key is nothing like the search that you
described. If the optimizer decided to avoid that index for that
search, it would have good logical reasons for that. Probably Arno
could predict which index(es) it would choose, I couldn't.

However, your custom plan provided nothing useful for the search,
only a forced (unnecessarily doubled) use of the PK index for ordering.

./heLen