At 11:04 AM 12/03/2004 +0800, you wrote:

>I have play around with putting some index. and the first thing I said
>is WOW. My question is putting an index in custid makes a lot of
>difference in the performance while putting an index in grandtotal
>dosn't affect after all. I don't get it why since the Grand Total field
>is also involve in the where clause...

If you have both ASC and DESC indexes on Grand Total, the optimizer might
choose it if it's appropriate in the case of each particular query; but if
it thinks another index is going to arrive at a faster result, it will
choose that.

If you look at the plans, you can see the decisions being made by the
optimizer. When you have a subquery, you will get two plans. Look at the
second plan to see which (if any) indexes are being used for the subquery.
Most of the time, the optimizer is right - even with your previous case
(where you had no index on Cust ID) - it manages to deal quite effectively
with non-indexed reads. The "demographics" of the data might even make an
index slower than a natural scan.

I don't like the EXISTS() test for these particular queries. EXISTS() and
its cousin IN() are not useful for subqueries that have to scan the whole
table, as is the case here. They are good for subqueries that test a
predicate and exit immediately a true result is returned and bad for those
that have to walk the whole table. I think a subquery that tests MAX() and
MIN() will find the result faster, provided you have those ASC and DESC
indexes on the tested column, because they are already optimized.

/heLen