Don't invent your own optimizer, just add '', to your ORDER BY clause. E.g. rather than writing

SELECT *
FROM BILLING_IMG_TABLE
WHERE LOCATION = 'XX' AND NUMBER = 123 AND IMAGE_TYPE = 'XX'
ORDER BY AS$IMAGE_NUMBER;

write

SELECT *
FROM BILLING_IMG_TABLE
WHERE LOCATION = 'XX' AND NUMBER = 123 AND IMAGE_TYPE = 'XX'
ORDER BY '', AS$IMAGE_NUMBER;

This should work almost equally well with dynamically created queries (tested on Firebird 1.5, not 2.1). I find indexes a lot more useful in the WHERE than the ORDER BY clause, and I think it is only in cases with a lot of rows returned that an index for the ORDER BY clause may be useful.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Stephen Boyd
Sent: 11. august 2009 14:21
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: slow query

--- In firebird-support@yahoogroups.com, Dmitry Yemanov <dimitr@...> wrote:
>
> It's simply lack of its intelligence. ORDER vs SORT is always a
> heuristic decision, not a cost based one. In your case, with a very
> selective search condition, it looks obvious to use SORT instead, but
> the optimizer is not that clever (yet).
>

So in the case where one is retrieving a small, ordered result set from a large dataset (realizing that small and large are relative terms) it would always be more efficient to sort rather than to order by an index?

That puts a whole new complexion on the way I should be structuring my queries. Up until now, I have been relying on the optimizer except in a very few exceptional circumstances where really complex queries caused the optimizer to create a sub-optimal plan. But this tells me that I should be specifying a plan whenever I want to retrieve a small, ordered result from a large domain. Hmmmm! That makes dynamically generated SELECT statements a bit more problematic. I would, in effect, have to code my own optimizer.