>>Why then does sys(3054) return partially optimized when I have Set Filter On
>>and I don't have a deleted tag....???? If I then set deleted off, my query is
>>then fully optimized...
>>Is VFP lying?????
>Of course not. But "fully optimizable" doesn't correlate to "faster" or "being
>a good thing".

A short quote from an internal post which I'm currently re-working into a MSKB article:

...If you repeat with SET DELETED ON, each query that was fully optimized will now show as partially optimized but the times are the same. It is usually better performance-wise to not create a DELETED() tag, even though it will cause the query to be only partially optimized if SET DELETED is ON.

Bottom line is:

Make sure your joins are optimized.
Have index tags on frequently used non-join WHERE condition expressions.
You won't usually get any performance gain from indexes on 1 byte or logical fields, or DELETED(), even though this may change the optimization level from partial to full.
Don't use joins if you don't have to.
TEST, TEST, TEST to make sure you have what you need index wise.
If you get equal performance with and without an index tag, and you don't need if for SEEKS or ordering, don't create it....