Bruce,

>No doubt you are right. But normally, this isn't an issue, that's why its worth looking at this time function now. That is, if we have a large table with a "lastname" field, and queries will be keyed on this field, we MUST have an index, whether the result set is 0 or 10,000 matches.

This depends on the resultset that is the result of the optimizable expression returned by the optimizer (because of other usable indexes). If the optimizer returns only about 100 records, the selection the lastname field does not have to have a index on it as VFP is fast enough to select the right records among these 100 records.

As I have stated before it depends on the seletivity of the index. An index in which an indexvalue selects only a small amount of records is far more usable than an indexexpression which contains only a few different values and return a large amount of records.

>But with deleted, we are in the opposite role, of having to decide whether or not to add an index, based on the predicted number of matches that could occur.

>The next step would be to decide at what point of maximum predicted x (from above) is it of value to add the index, considering the index overhead (space, possible slowing of queries). 10? Something like that, maybe...

I think you're formula should be a guideline to let the developer himself decide if the gain of performance is big enough to use the index on deleted.
The actual gain would depend on many factors like, width of the index, fragmentation of either the table or the index itself, internal memory, Harddisk, CPU etc. I don't think it is possible to determine an exact point where it would be wise to add an index.

Personally i think (again theory) that above the 10% - 20% you would notice the benefit of the tag in queries.

Just keep in mind that an index on deleted() only makes sence if there are reasonable amounts of deleted records within the resultset that is returned by the optimizer according to a optimizable expression.

IOW. If an invoice table contains about 10% deleted records but they all in the beginning of the file (invoice_date < {01/01/1997} and there is and index on this field) and the optimizer selects candidate records at the end of the file (invoice_date > {01/01/1997} so there aren't any deleted records within this resultset), it still doesn't make sence to use the TAG on deleted() as they were filtered out by the index on Invoice_date.

In large tables, it is wise to pack your table regulary so you wouldn't need the tag on deleted. By packing your table you 'll improve your performance more than by adding a tag on deleted()

Walter,