Hi Jerome,

> A SQL optimization question...

Firebird 1 or 1.5?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

> 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).
>