*snip*
>Here is the query:
>
>SELECT hpaph0.* FROM hpaph0, arappx ;
> WHERE hpaph0.aphst_ = arappx.appst ;
> AND hpaph0.aphcty = arappx.appcty ;
> AND hpaph0.aphpol = arappx.apppol ;
> AND hpaph0.aphyr = arappx.appcyr ;
> INTO CURSOR cResult
>
>Now, in my more recent tries, I have gone one step further. I now have index tags on the following fields:
>
>In hpaph0: aphst_, aphcty, aphpol, apyyr
>In arappx: appst, appcty, apppol, appcyr
>
>In other words, every field being used for the join is indexed. Here is what I get as the results from SYS(3054,11) when I run the above query:
>
>Rushmore optimization level for table hpaph0: none
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and table hpaph0 using temp index

Joe,
Here is my SWAG as to what VFP is actually doing. Take it for what its worth. It and $5.50 will get you something to drink at Starbucks. ;-)

As I said in a previous post, you don't specify any filter criteria. What is going on is you are getting two imtermediate result sets:

select * from hpaph0
select * from arappx

Neither of these is optimizable because you are returning all records from each table so a full table scan must occur. Then VFP (I think) builds a temporary index on the concatenation of the four fields (or possibly some subset of the four) used in the WHERE clause. It then has to once again table scan the hpaph0 and match up the corresponding records. If they match, then the record gets inserted into the final result set.

If arappx is very small, perhaps using it in a subquery will optimize the query. You'll have to play with it.

Good luck!