>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.
>

Hi Thomas,

The optimization algorithm is rather complex, it takes many variables into account: reccount(), optimized filter conditions, existing indexes, memory, the nature of the join conditions, etc.

Thanks,
Aleksey.