>>>>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.
>
>
>What could be the other way? BTW, I've checked where idea, and the result is none for both tables. In my criteria form I construct where condition as a seria of INLIST command, if number of selected items less than max_number. I definied max_number as 72. If user selects more than 72 items, I use temp table idea...
>

Hi Nadya,

I cannot help much w/o more info about data you're working with. According to your numbers one criteria generates 80,000 records wich is to much already.