>>Well, I found a banch of bad indexes with the expressions like alltrim(upper(field)) and filter with not deleted()...
>
>An index with the expression
not deleted() can make all sorts of queries very slow.
>
>An index with any other expression, but filtered on
not deleted(), will simply not be used for Rushmore Optimization. You may sometimes need this index, for example, for a client code, if you want to declare it as candidate (or primary, depending on your design), and the user supplies the code. In this case, it may be necessary (in some cases) to have a second index, not filtered and not candidate, on the same expression, for use in Rushmore Optimization.
I think it was somehow changed in VFP9. In any case, we almost never delete records in our system (we deactivate them), so we don't really need all NOT DELETED filters. Our database was developed by another developer, who is no longer with us. Over years we made lots of structural changes, but we never examined every single table (we have about 200 tables in the database). I just proposed to have another brain storming session to fix remaining tables. We just need to execute my colleague's code to produce an Excel with all the indexes in every table...
If it's not broken, fix it until it is.
My Blog