Re: Why does changing != to > slow things down so much?

Roy,

Since I do not know the structure of your indexes, I can only guess. However, in the first query, you will bypass the index on ID by using a !=, causing a full table scan (which is more efficient in this specific case). The second select, using an inequality (<), will attempt to use a range scan on the index, causing more I/O.

Try an explain plan on both statements, and you will definitely see a different path. If you DO run an explain plan, why don't you post the results as another reply? I'd appreciate it (and so would the lurkers).

Matt...

Roy Smith <roy_at_popmail.med.nyu.edu> wrote in article <roy-1807971833070001_at_mchip8.med.nyu.edu>...
>
> I have a table with about 900 rows...
>
> OK, I understand why this is happening. I figure the simpliest way to
 fix
> the problem is to change the "t1.id != t2.id" to "t1.id < t2.id". And,
> sure enough, that works. When I do that, I get back exactly the 21 rows
 I
> expect to.
>
> The problem is, the first query, with the !=, returns immediately. The
> 2nd query, takes about 45 seconds! Obviously, I've done something which
> is not very efficient :-) Unfortunately, I don't have any clue what it
> is. Why does such a trivial change in the query result in such a monster
> change in the response time?
>
>
Received on Sun Jul 20 1997 - 00:00:00 CDT