Re: sql query (all about indexing)

> You are saying "if (a = b) or ( a = somevalue and b = somevalue )".
> Cannot use an index for that logic either.

Wouldn't individual indexes on a and b improve evaluation performance of this expression considerably? And then if you also did a composite index for a and b you would get another significant improvement as well, right?

Also, how precisely does the engine get an improvement from individual indexing on the (a = b) term? Does it, for example, scan all 2M rows for different values of b and then do a (very fast) test for (a = bvalue)? So that's an O(n) operation, right? Where as (a = bvalue) is an O(1) operation.

So my understanding is that the entire expression above is O(n) if a, b, and a&b are indexed. But missing just one of these three indexes would suddenly turn the expression into an O(n^2), right? (Or perhaps you could get O(n) if one of a or b was indexed and a&b were composite indexed?)

Thanks,

Scott Received on Mon Apr 05 1999 - 15:37:29 CDT