Hi Aleksey,

>In order for an index to be used for a Rushmore optimization, an index expression should match an expression in WHERE. The word match doesn't mean that the text of the expressions, as they are written, has to be exactly the same or their byte code has to be exactly the same. It rather means that the result of executing both expressions for the same record is the same. To perform the comparison, VFP uses complex routine that ignores insignificant differences between expressions - the differences that do not cause differences in results.
>
>In some scenarios, there may be other requirements that also should be met. For example, collate sequence of the index should match current collate sequence, table's code page should match current code page, etc.
>
>I've tried to explain the rule, it is up to you to decide whether it is a simple rule.

Can you shed some light on the working of vfp's optimizer to decide the join order when FORCE is not set ? I think in some cases the joins are re-ordered in such a way that a temp index will be created on the intermediate table, while a usable index on a base table exists, but gets not used because of rearranging the query. Is the reordering mostly done on reccount() of each of the tables involved and is that reordering "decided" at start or at each step ? Is there a speed difference between the temp index and a usual .cdx also influencing that reordering ? I believe in some cases the re-ordered query IS faster, even if a temp index is created, in other cases not.

regards

thomas