On Fri, Oct 23, 2015 at 2:40 AM, drci...@yahoo.com [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
