On Wed, Jun 22, 2011 at 5:23 PM, kerryneighbour <kerry@...> wrote:

>
>
> - As you said, inner joins are much easier for
>> the optimizer to handle.
>
> Which is why I pared down my query to no joins at all in my original post. A simple query that just lists the complete table. It still uses un-indexed reads.

If you're reading the whole table, Firebird should never use an index.
Databases that cluster records on an index will use that index for a
full table scan, but Firebird doesn't use clustered indexes. Records
are stored on data pages in no particular order, so reading records in
index order can lead to bouncing around the file, reading an index
page, finding a record on one page, then reading the index again and
getting a record from another page {repeat for each record - one
reference to an index, one reference to a data page}. A table scan
starts with the first data page, returns all the records on it, then
reads the next data page, and so on. Much easier on the cache, much
lower I/O.

It's also faster to read the whole table, sorting as you go than to
read every record in index order. Since Firebird was designed, disk
transfer rates have grown less than processor speed or available
memory, making the chose between more disk traffic and sorting even
easier - use the efficient read and do the sort.

Good luck

Ann