Hi Hilmar

That's very good, but you can make the index size somewhat smaller by using INDEX ON IIF(DELETED(),.T.,.F.). It seems the IIF(DELETED(),1,0) must be padding the 1 and 0 in the index to something bigger than .T./.F. There is a slight performance improvement too ;) You'd have to change how you count the records to: COUNT FOR IIF(DELETED(),.T.,.F.) = .T.

>>this is why i asked the question. i have a 'desktop' that gives a 5 day working subset of the main data file. the relevant data is stored in a smaller file. as the items are worked on and completed, the main file is updated and the records in the sub file are deleted. the sub file is updated every minute with new data from the main file as new records are added and i do a check to see if there is 'x' amount of deleted records before packing the file. i wanted the fastest way to check how many deleted records there were before deciding on a pack.
>
>An index on deleted() will make this particular query faster, but it can also make many other queries slower: see FAQ #8109 for the reasons.
>
>To have an index that can help you find deleted records, but that will not automatically be used for Rushmore Optimization, you can do something like:
>
>

>INDEX ON iif(deleted(), 1, 0)...
>

>
>and count with:
>
>

>SET DELETED OFF
>count for iif(deleted(), 1, 0) = 1
>SET DELETED ON
>

>
>Note that, since the expression on the left, iif(deleted(), 1, 0), exactly matches the index tag, this index should be used automatically for optimization.
>
>Another option - and here you get the result instantly! - would be to continually keep the count of deleted records in a special table: use a trigger to increment the count, every time you delete a record, and reset it to zero as soon as you PACK.
>
>HTH,
>
>Hilmar.