Andrus,

1) You did not run the test with SET DELETED ON
2) 2^17 records is insufficient
3) Your data generates a totally bogus cdx file since there is only a single key value of blank for all records.

Trust me with real data and million record tables having a tag on DELETED() when the query is run with SET DELETED ON means the difference between a typical query running in a couple of seconds or a minute or two.

>David,
>I run the following test program several times.
>Results were in range 1.14 .. 1.26 seconds
>Sometimes first, sometimes second call runs faster.
>So there are NO difference if there is index on deleted() or NOT!
>How I can convince with the benefits of deleted() index if the
>test wil not show this ???
>
>close all
>set excl on
>set dele off
>set safe off
>create table test ( test c(200) )
>append blank
>for i=1 to 17
> copy to t
> appe from t
> endfor
>index on test tag test
>? "Without deleted()", testtime()
>index on deleted() tag deleted
>? "With deleted()", testtime()
>return
>
>* Return the total time
>func TESTTIME
>sele * from test into array t where test='x'
>dur = 0
>for i=1 to 100
> start= seconds()
> sele * from test into array T where test='x'
> dur = dur +seconds()- start
> endfor
>return dur
>
>>
>>Rerun the same test on a table with a few hundred thousand rows and you'll quickly convince yourself of the benefits of an index on deleted() if you run the query with SET DELETED ON.
>>
>>>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?