If you have FB1.5:

select saleprice + 0 , count(saleprice)
from presence
where productID = 1959201 and valuedate = '2004-04-01'
group by 1
order by 1

Ivan

----- Original Message -----
From: "Jerome Bouvattier" <JBouvattier@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, August 24, 2004 2:22 PM
Subject: [firebird-support] Sort precedence in execution plan


> Hello,
>
> A SQL optimization question...
>
> The following query operates on a table containing millions of rows. The
> where clause outputs a few dozen rows at most.
>
> select saleprice
> from presence
> where productID = 1959201 and valuedate = '2004-04-01'
>
> Having an indice on ProductID+ValueDate, this is very quick.
>
> But if I want to sort the results as follows :
>
> select saleprice
> from presence
> where productID = 1959201 and valuedate = '2004-04-01'
> order by saleprice
>
> The optimizer wants to use the indice that exists on Saleprice. What is
> highly inefficient in this case (several minutes to return).
> So, I tried to hint the optimizer that way
>
> select saleprice
> from presence
> where productID = 1959201 and valuedate = '2004-04-01'
> order by saleprice + 0
>
> This works ok, the optimizer chooses the right indice now.
>
> The pb comes if I want to do some groupings :
>
> select saleprice , count(saleprice)
> from presence
> where productID = 1959201 and valuedate = '2004-04-01'
> group by saleprice
> order by saleprice
>
> Then, I don't know how to trick the optimizer so that it picks the best
> indice (not the one on SalePrice).
>
> Can anyone help ?
>
> Thanks.
>
> --
> Jerome