>I tested this a number of times. Originally, I had the two switched. Sometimes when FoxPro has to do a number of memory intensive commands it can actually slow things down. I realize this is more likely with a lot of disk activity which you are not getting with the COUNT. Each time the test showed that the one with the EMPTY index is approximately twice as fast. My boss thinks that it is because EMPTY is returning only a logical, while the index on the entire field has to return the entire string.
>
>I don't know. I've read that having an index on DELETED() can be a mistake, as you suggested in your FAQ. Maybe if I have a small number of non empty D40CkNo instead of all empty this would change the results.
If I understand correctly, you still have your index on DELETED().
What
may be happening is something like this:
A) If you test on an optimizable query (Field = " ", or something), VFP gets values from the index for both expressions: 1) NOT DELETED(), 2) Field = " ".
B) If the expression is not optimizable (Empty(Field)), VFP gets values from the index only for the second expression (Empty(Field)).
In both cases, VFP gets the index keys for almost all records (??? this depends on the data in your tables).
It would seem that in case (A), VFP has more work to do. Once more, full optimization is slower. The situation might change radically if you killed the index on DELETED().
I suggest you re-run the tests without this offensive (for me) index (if you haven't done so already). I also suggest that you do the test under real-world conditions, i.e., presummably, over the network.
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)