>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.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)