Hi!

Because very nature of SQL, following is an equivalent using VFP code of processing of the last query:

select hpaph0
scan all
  select arappx
  locate for arappx.appst = (hpaph0.aphst_) AND ... && locate for join condition
  if found()
    && insert record into result set
    ...
  endif
  select hpaph0
  skip
endscan

As you see from the process above, optimization takes indexes from arappx table, not from main table, just because in the query main table scanned and records in the child table looked up.

You can swap tables in join, and you will see that query is optimized than.

>Hey all,
>
>Take the following SQL statement:
>
>SELECT hpaph0.* FROM hpaph0 ;
> WHERE hpaph0.aphst_ = 34 ;
> AND hpaph0.aphcty = 77 ;
> AND hpaph0.aphpol = 60051 ;
> AND hpaph0.aphyr = 1 ;
> INTO CURSOR cResult
>
>Using SYS(3054,11) VFP (version 6, SP3) tells me that table hpaph0 is FULLY optimized. This is to be expected since index tags exist for aphst_, aphcty, aphpol, and aphyr.
>
>Now, take the following:
>
>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
>
>The only difference here is that I have added a table (to do a join) called arappx. Instead of using literal values I am using fields from arappx in order to do a join. For this, VFP tells me that table hpaph0's optimization is NONE. What gives? I was under the impression that as long as Fox could match what was on the left side of the comparisons to index tags, some Rushmore would kick in, at least for one of the tables. Am I high? The table arappx in this example has no indexes whatsoever, but shouldn't Rushmore still be able to optimize because of the hpaph0 tags? The arappx table is only around 5-10 records.
>
>If I am just way off, how does one do an optimized join such as this? I tried using INNER JOIN syntax, thusly:
>
>SELECT hpaph0.* FROM hpaph0 INNER JOIN arappx ;
> ON hpaph0.aphst_ = arappx.appst ;
> AND hpaph0.aphcty = arappx.appcty ;
> AND hpaph0.aphpol = arappx.apppol ;
> AND hpaph0.aphyr = arappx.appcyr ;
> INTO CURSOR cResult
>
>And got the same response -- zero optimization. Could somebody enlighten me as to where I am going wrong here?
>
>Thanks muchly.
>
>JoeK