>Hi Fabio,
>
>interesting cases.
>
>I changed
>- sys(3054,11) -> sys(3054,12), although there is no join, I don't use 1 or 11 any more. Doesn't hurt.
>- after indexing, I always SET ORDER TO. All the chapters about rushmore say, rushmore works best if no order is preset/suggested. In fact I didn't see any difference with or without that, again doesn't hurt.
>- changed INDEX ON FF FOR NOT DELETED() TAG TFF2 to INDEX ON DELETED() TAG TFF2 BINARY
>- changed INDEX ON FF FOR NOT DELETED() TAG TFF2 to INDEX ON "Blah" FOR DELETED() TAG TFF2
>
>I still get 1. select fast, 2. select slow, 3. select fast.
>
>But all in all I see no bug! Let me explain:
>
>1. Select with max or count Sys(3054) states no optimization, which is true, as the message only states no use of an index for filtering purposes. It uses TFF1 for MAX(FF), but still the table is used unfiltered. That is also what I meant with "irritating message". You get messages for filter/where clauses and join/on conditions only, other optimizations aren't displayed by Sys(3054).
>
>=> no bug, just misinterpretation of sys(3054) output. The worst thing you could say about it is, you are misinformed about what's being done by rushmore.
>
>The lines "Rushmore optimization level for : ..." are only stating what optimizations where done for the (virtual) Where Clause, nothing more, nothing less. It's misleading, as it reads as if it would be the all in all rushmore effort on this alias. It is not, it's just the part of rushmore regarding filters.
>
>2. Select is not optimized due to SET DELETED ON. As rushmore can't guess how many records are deleted and if the max(ff) is found within the undeleted or deleted records, I think this can't be done better for the general select. Not to speak of count(), as that would need the number of deleted records. It even wouldn't help much to include the number of deleted records in the table header, as that could only be used to optimize selects on unfiltered tables, as soon, as there is a where clause other than not deleted(), this won't help.
>
>=> no bug. And optimizing such special cases won't help in general.
>
>Again the message "Rushmore optimization level for intermediate result: none" only regards the virtual where clause: where not deleted(). As there is no index usable for this filter, it's a full table scan´, no optimization.
>
>3. Select is optimized with tff2 for filtering out deleted records, maybe tff1 is used additionally, as in 1.select we don't get a message about that. indexing "for not deleted()" or "on deleted() binary" does not make much difference. But an index ON FF FOR DELETED() TAG TFF2 is making it fast, very fast! This index is of cause empty, as there are no deleted records, that is much more helpful for rushmore than both other indexes!
>
>=> no bug. Again only misinformation. Due to the nature of the additional virtual clause "where not deleted()".
>
>
>All in all the only thing missed is information on rushmore about additional optimizations, as the optimization of MAX() or COUNT().
>
>
>Regarding the Count(ff) variants of your selects, all of them return 100001 for me (? AX). What did you mean with BAD/GOOD in your sample code, Fabio?

BASD/GOOD SYS(3054) OUTPUT
>
>All you can learn from this is, that everything works much easier if SET DELETED OFF and there is no additional virtual clause rushmore has to optimize. So a vfp developer should use a strategy that enables him to SET DELETED OFF, as that is always much better for rushmore. Record recycling is the way to go, to keep the number of deleted records low. And you need a self defined deleted() mark, for example negating id's for deleted records. You can always sort deleted records out that way in selects that should not include deleted records and you can find deleted records for recycling easily.
>
>An INDEX ON "Blah" FOR DELETED() seems also a good idea in conjunction with record recycling, even better than a binary index. (you may replace "blah" by any other constant).
>

Try this

INDEX ON .NULL. FOR DELETED() TAG TNULL

>
>Regarding my suggestion of ASHOWPLAN() I'd inlcude optimizations other than filter/join to the optimization type column of the suggested array, for example output into that array column could be "filter alias.name='alekesy'" "join alias1.id = alias2.id" or "expression MAX(ff)".
>
>Bye, Olaf.