Subject Re: [firebird-support] How to index?
Author Martijn Tonies
> > In general (Arno will disagree with me here), I think that
> > indices should be used for quick lookups (WHERE <condition>)
> > and sorting should be done only via an index if it makes sense.
> Wouldn't it be faster using the index to group together records and
> do the sum, instead of sorting it and doing the sum? I mean, if
> the data is already properly indexed (sorted), why do sorting
> anyway? It should be faster, but then again I don't know the
> internals.

The sorting itself might be faster - but getting
the actual data isn't.

When retrieving data in "natural" order, the
table data is fetched, page by page, as stored
on disk.

When retrieving data in "indexed" order, the
server is moving towards the index page (where
the index data is stored), reading it, moving
towards a data page, reading it, etc... This is
slower for lots of rows compared to "natural".

Now, the grouping needs to be done despite
the index.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions