>> With all do respect, i don't think you understand the working of rushmore.
>> If only 1 part of your expression is optimizable (combined with AND) rushmore
>> will optimize this. I strongly suggest reading 'Optimizing Rushmore
>> Expressions' chapter 15 (in the developersguide of VFP 5.0)

>Well, believe me. I've read this chapter several times. ;) My example I sent to Jim should give you a good overview over the facts.

>Well, Then you will know that you don't have to have an index on deleted to take advantage of rushmore (like you stated) rushmore will partually optimize the expression. And like i stated partually optimized expression don't have to be slower than fully optimizible expressions.

If an optimizable expression made about 99% of the selection, it would not lead to significant performance degration if the 1% was made by sequentual testing this (already smal) resultset.

>I'm sorry for replying to your message. Please excuse my interruption, but my understanding was that if somebody sends a message on the UT, everybody may feel free to answer. Especially if it's such a controversial message as the one you sent. Apparently I was wrong. I promise not to do it again... :)

I didn't mean to be rude, and of course you're welcome in this thread. I'm not (like you) not native english spoken. Sorry is this lead to some missunderstanding. I just felt that there was a lot of missunderstanding regarding this topic and i just want to clear this out.

> Your misunderstanding of rushmore is exactly the reason i wrote this message.
> An expression does not have to fully optimizable which don't have to mean
> that it would be slower than a fully optimizable expression

Well, this is a rather interesting statement. :) Let's see: 'Partially optimized queries are equally fast as fully optimized ones'. This sounds like a paradoxon to me. :)

Like i said here before. If the resultset is made by an (partually) rushmore optimizible expression which gets about 99% of the entire resultset. it wouldn't be hard to select this set on the deletion status.

>The fact of the matter is that even very small and simple queries run essentially faster when they are fully optimized then when they are partially optimized. The simple example I sent to Jim runs twice as fast fully optimized then when it's partially optimized (I just tried it). Even though there are only 92 records and none of them are deleted. The interesting part is that when you remove all indexes and run the query again (which is now non-optimized), you can hardly see a difference to the partially optimized version.

>Now you can say that this is only because it's a small table, and you have a point there. However, it already shows that the difference between fully optimized and partially optimized is at least as big as the difference between partially optimized and non-optimized. At least in many cases. And that's enough reason for me to add the Deleted() index.

I still think you're wrong. If you've got and fully rushmore optimizable expression like:
SELECT * FROM invoices WHERE Date>{01-01-1997} AND Article="MC CHICKEN" and clientnr=4560

this would be fully optimizible and run for e.g. in 5 seconds

When i change the statement in:

SELECT * FROM invoices WHERE Date>{01-01-1997} AND Article="MC CHICKEN" and clientnr=4560 AND number # 3000

where Number # 3000 is NOT optimizable but will only affect one record in the prior resultset, the query will likely run just as fast as when you have a index on count.

IOW Partial optimizible expressions don't have to be slower than fully optimizable expressions.


regards, Walter