Hi everybody,

OK, the old topic again. *g* I did some more tests and I can now prove that:

a) VFP uses all optimizable expressions to determine a possible result set, not the final result set. After that it reads all records found via Rushmore and verifies that the record really match its index entry. The purpose of this postscan is probably to catch records that have been modified in the time between building the bitmap and reading the record.

b) The postscan is not performed when you set a filter or when you count records via COUNT TO or SELECT CNT().

c) If the filter condition is "NOT something", VFP reads the NOT part of the index, and not the "something" part and ingores those records. IOW, a filter on NOT DELETED() reads all non-deleted records from the index and does not (although it would probably better) determine deleted records and flag them as invalid in the bitmap.

d) SEEK operations and probably also internal SEEKs as part of a relation do not use an index on DELETED(). VFP finds the desired record through the index and sequentially reads all matching records until it finds a non-deleted index. VFP performs no postscan on a SEEK result, it only reads the deleted flag in the record.

I've verified this by manipulating the CDX and DBF files with a hex editor and not be measuring how much data volume is transferred.

The conclusion:

a) If you have SET DELETED ON and an index on DELETED(), VFP reads the index entries of all non-deleted records. If this block of data is larger than the amount of data that VFP has to transfer in deleted records of the result set, the index on DELETED() slows down the query. Determining whether this is the case is not as easy, as caching and multi-user issues come into play, specifically how often VFP has to actually retrieve the deleted index as opposed to retrieve it from a local cache.

b) If you need to find specifically deleted or non-deleted record (eg, for recycling) or you need count operations, an index on DELETED() speeds up these operations dramatically and should be considered a requirement.

Christof