--- In firebird-support@yahoogroups.com, Marek Konitz <marekk@s...> wrote:

> Hello,
>
> I've checked performance analysis done by IBExpert and noticed curious
> thing:
>
> select * from TABLE
> results in n non-indexed reads1
>
> select * from TABLE order by PRIMARY_KEY_FIELD
> is n indexed reads.
>
> Is there real performance difference?
>

Yes, the first query returns the records in whatever order they happen
to be stored. This will normally start off sequential, but will go out
of sequence as records are deleted, garbage collected, and re-used. It
you don't particularily care about the order of the records, then not
using the order by clause means that Firebird doesn't have to read the
index.

That said, it is pretty much always quicker to sort on a field if that
field is indexed. The primary key field has optimum selectivity, and
therefore the optimiser made a good choice to use the index to put the
data set into a particular order.

Whether this is noticably slower or not depends on a million factors,
but fortunately for you, these queries are not interchangable. If you
care about the order, you must use the second query. If you do not
care, then using the second query may cause it to run slower, and so
should be avoided.

Adam