On VFP9, the Rushmore engine uses a

index ... FOR NOT DELETED()

for a full optimization.
But when, in a INNER JOIN SQL SELECT
the query it is fully optimized, VFP uses an not optimized algorithm
that of fact doesn't use the indexes in efficient way.

Repro code, the FORCE clause is need for show the bug effect.

#DEFINE NBOUND 20
#DEFINE NRECORDS 300000 && with 1000000 the time of bug becomes 10x

CLEAR 

CREATE CURSOR OrdersItems (id_Orders I DEFAULT Orders.id_Orders, ItemInfo1 I DEFAULT  100000*RAND())
CREATE CURSOR Orders (id_Orders I AUTOINC, OrderInfo1 I DEFAULT 30*RAND())

FOR K=1 TO NRECORDS 
	APPEND BLANK IN Orders
	APPEND BLANK IN OrdersItems
NEXT

SYS(3054,11)
SET DELETED ON

SELECT Orders
COUNT FOR OrderInfo1<NBOUND
? "Orders Prefilter Intermediate results",_tally at 40
SELECT OrdersItems 
COUNT FOR ItemInfo1<NBOUND
? "OrdersItems Prefilter Intermediate results",_tally at 40

? 
? "WITH A JOIN INDEX"
SELECT OrdersItems
INDEX ON id_Orders TAG FK_Order
TEST()

? 
? "WITHOUT INDEXES"
SELECT OrdersItems 
DELETE TAG ALL
TEST()

? 
? "WITH PREFILTER INTERMEDIATE RESULTS PARTIAL OPTIMIZATION"
setorder(.F.,.F.,.F.)
TEST()
setorder(.F.,.T.,.F.)
TEST()
setorder(.F.,.F.,.T.)
TEST()
setorder(.F.,.T.,.T.)
TEST()
setorder(.T.,.F.,.F.)
TEST()

?
? "The BUG: fully optimized, but really a cartesian time execution"
setorder(.T.,.F.,.T.)
TEST()
setorder(.T.,.T.,.F.)
TEST()
setorder(.T.,.T.,.T.)
TEST()
SYS(3054,0)

PROCEDURE TEST()

T1=SECONDS()
* uses FORCE for show the bug, IT IS NOT THE BUG SOURCE
SELECT COUNT(*) ;
INTO ARRAY ARES;
	FROM FORCE Orders O INNER JOIN OrdersItems OI;
		ON OI.id_Orders=O.id_Orders;
	and	OI.ItemInfo1<NBOUND;
		AND O.OrderInfo1<NBOUND
? "Time:",SECONDS()-T1,"Count:",ARES

PROCEDURE setorder(for1,for2,join2)
SELECT Orders
DELETE TAG ALL
IF m.for1
	INDEX ON OrderInfo1  TAG OInfo FOR NOT DELETED()
ELSE
	INDEX ON OrderInfo1  TAG OInfo
ENDIF

SELECT OrdersItems
DELETE TAG ALL
IF m.join2
	INDEX ON id_Orders FOR NOT DELETED() TAG FK_Order
ELSE
	INDEX ON id_Orders TAG FK_Order
ENDIF
IF m.for2
	INDEX ON ItemInfo1 TAG OIInfo FOR NOT DELETED()
ELSE
	INDEX ON ItemInfo1 TAG OIInfo
ENDIF
RETURN

Strange also the fact that the solution that builds the temporary index is more rapids
of that that already uses the ready index,
and it is more rapid of the alone construction of the index!

Can VFP build a optimized filtered Index ?

INDEX ON id_Orders FOR ItemInfo1<NBOUND TAG Temp

Fabio