I run the following test program in VFP 5.0a:

close all
set excl on
set deleted on
create table test ( test c(1) )
index on deleted() tag deleted
index on test tag test
sys(3054,1)
sele * from test into cursor t where test=''

Output is:

Using index tag Test to rushmore optimize table test
Using index tag Deleted to rushmore optimize table test
Rushmore optimization level for table test: full
Selected 0 records in 0,36 seconds

So you are wrong: rushmore seems to use index on deleted() last
and this cannot affect the performance if there are few deleted records !?!?

>I didn't test before posting this (no data handy), but I believe that you will notice a significant difference whether or not you have any deleted records in the table. If you don't have an index on deleted(), fox doesn't know that you don't have any deleted records, and has to check each one in the result set.
>
>It would be interesting to see which order the VFP SQL engine evaluates candidates for the results set: does it check the deleted status first, or does it check the conditions in the WHERE clause? If it checks the WHERE clause conditions first, you may be right: the number of deleted records would have minimal impact on the query performance. But I think that you'll find the opposite if you test. I vaguely remember learning this the hard way when packing and reindexing the tables at a client's site, and something went wrong and the DELETED() tag got left out of the reindex routine. Even though there was not a single deleted record in either table, SQL performance slowed to a crawl. Twas restored to its previous glory when the tag was restored.

Do you really think that if there is no index on deleted(), fox
scans the whole table? I have no indexes on deleted() and the whole
source table is never scanned.


>
>
>>If you don't have a lot of deleted records, you won't notice much difference but if you do, having a tag on DELETED() is well worth it. It really does speed things up.
>>
>>
>>>I'm using a lot of sql selects to select data from big free dbfs.
>>>Those tables have very few deleted records and where clauses are written so
>>>that Rushmore finds always a index tag to use.
>>>SET DELETED is ON always.
>>>
>>>Will the performance increase if I add
>>>
>>>INDEX ON DELETED()
>>>
>>>to those tables? Will select queries run then faster?
>>>I have read some recommendations to have index on deleted() in each table
>>>to avoid sequential scanning of the deleted records by select. Is this true?