>>I would add an index on deleted too
>>
>
>
>Even after reading this http://fox.wikis.com/wc.dll?Wiki~NonDiscriminatingIndex ?


I did not read that, but it seems that most of it was written before the binary keyword was added, and then the comments that include it seems to agree there is an advantage (but I just skimmed the text)

Anyways, I just changed the test to have very few records (0.1%) deleted or most of them deleted (99.99%) and run the tests, also added a test to encapsulate the count call on a UDF.

FUNCTION CountDeletedRecords(tcAlias as String) as Integer
	LOCAL lnSelect, lcAlias
	
	lcAlias			= IIF(VARTYPE(tcAlias) = 'C', m.tcAlias, ALIAS())
	lnSelect		= SELECT()

	SELECT (m.lcAlias)
	COUNT TO m.lnRecords
	SELECT (m.lnSelect)
	RETURN m.lnRecords
ENDFUNC

The results (granted, not a very scientific test) seems to support the inclusion of the index

Records		Index	Time 1	Time 2	Time 3	Time 5
2958		Yes	0.011	0.001	0.019	0.001
3095		No	0.451	0.226	0.443	0.226
2994173		Yes	0.015	0.003	3.980	0.003
2994026		No	0.796	0.320	3.929	0.317

Hugo