>Dan,
>
>SNIP
>>I think this clearly demonstrates what you are saying. If you have one condition that has a small number of index keys and one that has a large number of index keys, you are better off having just an index on the set that returns the small number.
>>
>>As you have stated though, the data is changing all the time. Is the penalty that I am getting now about .1 to .2 worth constantly monitoring the data basically doing what Oracle or SQL Server would do - Kind of a crude statistics. I don't know.
>>
>Since this seems to be somewhat critical to you, it may well be worth "monitoring" intermittently. But you could probably devise a program to do this for you during off-hours, checking the current data content for known regularly used search criteria and evaluate if action is needed.
>Of course you would also have to store the search criteria actually used in some table to ascertain which field(s) occur most often.
>
>One thing I encourage you to do... the UT "Documents" section of this (VFP) forum has a document for "VFP 8 Wishes" and it is regularly reviewed/forwarded to MS' VFP Team. You could add 'cost-based optimization' (or whatever you would prefer as an enhancement) to the document.
>
>good luck,
>
>JimN

Those are good ideas. I also think it would be nice to have in the select a NoOptimize per clause. In that way you could keep your indexes, because clearly there are times that even when returning a large set, you still need the index.

You had also said something about a FORCE in VFP 7.0. Can you talk a little more about what exactly the FORCE does?