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