On Fri, Oct 23, 2015 at 2:40 AM, drcih87@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

I don't think I understand you fully. Indexed reads a hudreds of times faster than non indexed.

Yes, when you're looking for a specific value.  When you're looking for something that is NOT a specific value, then it's often faster to read the table in its storage order rather than reading through an index.  In fact, if I remember correctly, the Firebird optimizer will never choose indexed access when the lookup is based on non-equality.

 

And the table will have millions of records, so I do need them.

Reading millions of records through an index is unlikely to be faster than reading the same millions in storage order. 

Normally, when Firebird reads records through an index, it constructs a bit map of the record numbers of records that match the indexed condition.  The record numbers include the page on which the record is located.   If the bitmap include 90% of the pages in the table, Firebird will then read the table in storage order (which is also record number order) skipping 10% of the pages, and the work that went into building the bitmap is completely wasted.

"then this is cheaper to scan table (500 records) then use 400 indexed reads"

What do you mean scan table? How do I do that? Like a stored procedure with for select? I don't understand, please explain.

You don't have to do anything.  The Firebird optimizer analyzes your query, attempting to find indexed paths to data.  If it doesn't find usable indexed access, it resorts to a table scan.

Good luck,

Ann