>>Just for a fun can you try WHERE instead of JOIN?
>>BTW, How many records are in the temp table and how big result set do you expect?
>
>I'll try at home with the test table. The temp table has 110 records. The main table has 5mln records. I think, the resulting set would be huge, may be 800000 recs. This is just a plain test, the live query uses 7 joins to tables, each of them has ~3-5mln. recs... :(
>

Just a guess, but it seems to me that foxpro doesn't have any reason to use index for temp table. It would rather scan it and use index on main table to get the related records. You should find some other way to limit the number of records in the result set.