David,

>It was not a "myth". It was/is a fact the thread you reference give concrete examples where it does help. You yourself give some examples where it can be beneficial.

It was a myth because in 99% of all cases it did nothing to enhance performance or was slowing queries down. I've identified three cases in where it might be beneficial which are on itself exceptions. The only one that does make sense is the COUNT statement.

>A blanket statement that an index on deleted() is of no use is just as bad as saying a table should always have one. The real fact is it all depends. The developer needs to test to see whether it overall helps or hinders performance.

A developer should understand when to use indexes and how they are used. This is the main problem here. I wrote the messages back then because I knew how the indexes were used and that the general accepted knowledge was humbug because the logic was false.

Many people tested on their local machine in stead of a network where the network is the bottleneck, not giving reliable indications of its performance in production time. Also many people are not aware of network optimizations like exclusive oplocking so that even when testing over a network they get false measures.

I was stunned back in 1999 that so little people (in fact I encountered none at the time), who really understood how rushmore optimized queries. Even from the side of MS there is no usable explanation telling you this. If people knew a littlebit more about databases and indexes in general it would not be such a great problem.

>Also your admonition against the tag on deleted() may fall by the wayside once VFP9 with it's new binary index attribute ships. I will admit right now that I've not yet done any performance testing on binary indexes yet primarily because of the debug code still in the beta release.

Unfortunately, the binary index is a miss. Though much smaller it really does not adress the problem: Low selectivity and therefore it does not change much to the story. If the deleted() tag is not going to weed out (many) deleted() records it use is useless. People are given a false indication that full optimization is better than partial optimization.

However, Theorectically there is only one advantage in full optimization and that is something called "full index coverage" (E.g. SQL Server) where the information processed by a query is processed by the index alone. VFP, up until VFP8 has not been using this technique and since it will require a major redesign of rushmore and ensuring index correctness I don't see that comming soon.

Walter,