Bela,

>I guess we try to get to know a black box. I question is, How smart fox is to optimize? If Deleted is the first that fox is check (and result set in percent is high), the Deleted tag is usefull, but if deleted status check is the last step, (and result set is low in percent) deleted tag has very low impact on performance.
If someone of creators of fox would answer to this thread, we could be sure about it. Is not it?

No it's not. The optimizer makes use of the indexes by loading the index information first and determine the optimizable resultset. Only after this it will load the actual records to apply the non-optimizable filter.

If there are not too many deleted records within this resultset there would be no benefit to the deleted() tag as the resultset without the deleted tag would be simular in size as the with the deleted tag. So from a Rushmore point of view there is no need for a index on deleted().

The are some circumstances where an index would benefit the performance:

- When you have SET DELETED on AND want to make use of the filter capability of the SQL SELECT statement
- You have lots of deleted records in your table (or tables), lets say more than 5 - 10%

Maybe the optimizer can optimize the following

- You want to COUNT records with SET DELETED ON (as the optimizer doesn't have to count the actual records but only the index key's)

As these circumstances should be fairly rare (as you don't have to maintain lots of deleted records in large tables) you would generally NOT need any index on deleted()

PS. I did not try to check this on a black box manner. I did only want to describe how the optimizer uses indexes to optimize query's in theory.

Regards,

Walter