Ed Dressel wrote:

>
> I have a table where I need the maximum value from. I ran "select
> max(LastUpdated) from TableName" SQL against it and got the following
> stats:
>
> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 15ms

That's reasonably quick.

> Reads from disk to cache = 0

And that's why. All data pages and pointer pages were already
in cache. Since the purpose of indexes is to reduce the number
of reads from disk, you won't see much - if any - benefit from
an index.

> Writes from cache to disk = 6

That's a bit odd. It could be garbage collection or transaction
management. Starting a transactions causes a write of the
database header page and ending one causes a write of the transaction
inventory page.

> Fetches from cache = 392

You will get fetches from cache for each data reference, for
the data references required to prepare the query for execution
and for transaction and system management. I'd guess the table
was fewer than 300 records. A cache fetch is a memory reference
- very close to free.

>
> Then I created an index on LastUpdated:
>
> Create Index TableNameIdx1 on TableName(LastModified);

As Lukas pointed out, only descending indexes are useful for
finding the MAX value. And, in this case, the whole table is
so small that locating and reading the index would be as
expensive as a natural scan.

> ... both have the same number of fetches from
> the cache.

No surprise there ... Firebird is preparing the same query and executing
the same plan.


Regards,


Ann