Hi dmtiry,

>Like Jon, I have never really done any testing of the speed of having DELETED() tag vs. not having. But you make a very interesting point about whether table has deleted records or not. What it means is that if I drop DELETED() tag and a customer has speed problem, they can pack the table, which should make the DELETED() tag unnecessary.
>
>The reason I started looking at this index tag is I was looking for a way to reduce number of index tags in one of the tables. The table has up to 300,000 records (and will grow to a million). It already has 15 index tags and I need to add another one, on Char field of 25 char length. I was/am concerned that this size table with so many tags (16), may slow things down a lot of cause table corruption.

>Thank you very much for your suggestion.

16 Tags is not unusual. But I don't see how an index on deleted() could help speeding up your queries. Esspecially in large tables (as in your example) you're better off without it. The best thing is to anayze which indexes you really need.

The thing is about selectivity. An index with a high selectivity (an indexvalue that only refers to a few records) is way and way better to optimize your query than one with a low selectivity (an index value refers to a lot of records). Boolean indexes tend to be near useless for rushmore optimization because of low selectivity.

If you run a query be sure your query is optimized by high selective indexes and avoid low selective ones. The performance of rushmore is determined by the least selective indexes. So, if you've got an index on DELETE() it will force all indexesnodes with a value of .F. (non deleted record) to be downloaded to the client. And esspecially with million record tables this can be very time consuming.

Lets say you've got a million record item table, which contains among other field an item column (indexed primary key) and you'll do a (for example on a network).

SET DELETE ON
SELECT * FROM items WHERE ItemID = "BIKE"

Without an index on deleted, it will just examine the ItemID index, will find one record to retrieve and get you the record in a fraction of a second.

With an index on deleted, it will examine the ItemID index, but also the DELETED() index which contains about a million records to be downloaded and joined with the result of the ItemID index. This latter step is very time consuming and not neccesary at all.

Just read Thread #196021 and Thread #198031 for more discussion.

Walter,