Hi,

> >> Yes, use order by instead of group by.
> >>
> >> Just selecting an indexed field doesn't convince the optimizer to use
> >> the index ;-)
>
> > Even more - WHY should it read indexed if you don't use any
> > WHERE clause ... you want all rows, Firebird/IB will give it to
> > you and in that case, a "natural" (unindexed) read is fastest.
>
> If you want the result set ordered, I think it's quicker to use an
> index to sort, if one exists for the column. Otherwise, Firebird/IB
> will have to sort the result set using another logic, and that logic
> might be slower than using an index. Definitely, if you don't want the
> result set ordered, why bother trying to use an index(other than for
> the WHERE clause).

(my reply was besides the ORDER BY stuff, but...)

An in-memory sort (order by) after reading all rows is faster than
reading the rows in index-order... That is, IF you want all rows.
An indexed read can be faster if you just want to have the first
couple of rows of your resultset and not the complete result.
Somewhere there should be a point where things tip the scale...


With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."