--- In firebird-support@yahoogroups.com, Jakub Hegenbart
<Kyosuke@s...> wrote:

> I'm just asking myself why is the ORDER BY
> SPREAD_ID, ACTION_TIME necessary. Could the optimizer be tweaked to
> infere that if there is an equality on first n columns of an index,
> the n+1 column can be ordered using ORDERED read in such cases?

No, FB just now can't use the same index to join/filter and to sort.
But in some conditions this can FB clone Yaffil. Work on merging codes
should start just after FB1.5 release.

BTW, FB don't accept explicit plans like

Plan(
Table1 Order Index1, /*to sort*/
Table1 Index (Index2) /*to join or filter*/
...
)

but this is only syntax limitation, in fact optimizer often builds
such a plans itself but when reporting plan shows only usage index for
sort :) To check this Dan can create separate index (say, New_Index)
on Action_Time and make

select first 1 SpreadAction_ID from SpreadActionTable
where Spread_ID = 9007 AND ( Status IS NULL OR Status != 2 )
AND Action_Time >= 1073030400
AND Action_Time <= 1075535999
Order By Action_Time

and then

select first 1 SpreadAction_ID from SpreadActionTable
where Spread_ID+0 = 9007 AND ( Status IS NULL OR Status != 2 )
AND Action_Time >= 1073030400
AND Action_Time <= 1075535999
Order By Action_Time

to exclude hidden usage of SPREADID_INDEX. Most probably he will get
the same plan - Plan (SpreadActionTable Order New_Index) but first
query will be fast and second will work for ages :)

Best regards,
Alexander
Firebird Foundation - http://www.firebirdsql.org/ff/foundation/