--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Sean 'not the great Canadian' wrote:
> > --- In firebird-support@yahoogroups.com, "Leyne, Sean" wrote:
> >> Sean, (What a great name!)
> >>
> >>> I need to furtherly join table_A to a small table with only 5

records.

> >>> Now the query is revised as:
> >>>
> >>> select first 250 * from table_A A
> >>> left join table_B B
> >>> on A.column = B.column
> >>> where A.time > sometimestamp
> >>> order by A.ID desc
> >>>
> >>> Now it takes more than 1 minute :-(
> >>>
> >>> Can anyone please point out what is missing here?
> >> The FIRST predicate is evaluated based on the *results*.
> >>
> >> So, you're query is performing a full join for both tables, then
> >> returning the first 250 rows.
> >>
> >> Sean
> >>
> > Yes, a full join of two tables is what I want to. When running the
> > query above, a plan was printed in IBExpert:
> >
> > PLAN SORT (JOIN (TABLE_A NATURAL,TABLE_B INDEX (PK_TABLE_B)))
> >
> > So the descending index is not used in the query...
>
> The optimizer doesn't know that there will be maximum 1 row in B that
> matches the current row in A (I don't know either, I'm just guessing).
> In theory, there could be 250 rows in B matching the highest A.ID, so
> that only the very first row of A should be returned. If my guess about
> 0 or 1 matching row in B for every A, and that you're only

interested in

> one value (or very few values in B), then you could try
>
> select first 250 A.Field1, A.Field2,...,
> (select B.Fieldx from table_B B
> where A.column = B.column) as B_Field
> from table_A A
> where A.time > sometimestamp
> order by A.ID desc
>
> Maybe the optimizer will start reusing the index, but I've never had

the

> kind of problem you're experiencing, so I do not know for certain.
>
> Another workaround of your problem, can be if you know that the first
> 250 will more or less always be amongst the last 1000 IDs, then you can
> find the maximum ID and simply add 'and A.ID > MaxID - 1000' to your
> query (though don't add a subselect to find MaxID in your 'select first
> 250' query, that will just be additional overhead).
>
> HTH,
> Set
>

Yes, the assumption perfectly fits me, and the subquery works like a
charm. Now the optimizer picks up the index. Thanks a lot!