Subject Re: [ib-support] SELECT COUNT(*) ... - slow
Author Lista de Discução Interbase

Just a shot in the dark:

When FB count the records when you give a "Select count(*) from aTable"
The engine needs to scan ALL data pages that belongs to that table right ?

BUT IF it's only scans the Index Pages (choose the smallest index) so the
engine will reach a bigger number of record on each page, therefore it will
scan a small number of pages, I think it could improove the speed.

And if we send a "Select count(*) from aTable where Name like 'John%'" the
engine will seek for John's on index pages then go the datapages to count
the data, or will only count the record from the index references ?

I have read once that Oracle only read the indexes if the indexes already
contains all the information the select wants, does FB work i the same way ?

So if you have a Select Name from aTable where Name Like 'John%' only
trough a indexes search Oracle returns all names, I think it's a very good
way to optimize page reads.

This aproach will be great in situations like that

Select Max(Item) from Order_Items where OrderID = X

Normally I have an Unique Index on (OrderID, Item) to avoid duplicate item
number in the same order. So just scaning trough the index pages it can
know what is the max value, or the count value, etc.

Sorry for my bad english.
I hope you could understand what I want to say. ;-)


At 21:04 05/04/02 +0100, you wrote:
>And that's the only way. FB does not have a counter of the number of rows