similar queries, big difference

[Shane Niebergall]

Can someone explain why there is such a big performance difference in these
two queries? Note: I have a multicolumn index on ltid and inuse.

mysql> UPDATE leads SET inuse='0' WHERE inuse!='0' and ltid='8' and
inuse<'10982988 63';
Query OK, 0 rows affected (46.35 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE leads SET inuse='0' WHERE inuse='1' and ltid='8' and
inuse<'10982988 63';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

The one that tests by ='1' is almost instant, while the one that tests !='0'
takes 40 seconds! Is this just something strange, or is there some principle
that I am missing?

I just noticed that using "WHERE inuse>'0' " also is instanteous.

Thanks!!
Shane

[Bill Karwin]

Re: similar queries, big difference

Shane Niebergall wrote:
[color=blue]
> The one that tests by ='1' is almost instant, while the one that tests !='0'
> takes 40 seconds! Is this just something strange, or is there some principle
> that I am missing?[/color]
[color=blue]
> WHERE inuse!='0' and ltid='8' and inuse<'10982988 63';[/color]

The guess I can come up with is that != creates two intervals, while =
and > create one interval. That is, one interval is from -inf to 0
(non-inclusive), and the other interval is from 0 to 1098298863
(non-inclusive).

Whereas if you use inuse=1, it only needs to do one interval. In the
case of using inuse=1, the other condition on inuse can be factored out,
since it is always true if the first condition is true, and irrelevant
(because of the AND) if the first condition is false.

I'm not knowledgeable enough to know why two intervals would cause such
a dramatic performance difference over one interval. This is just my
best guess.

There's a bunch of technical detail on the MySQL site about how range
conditions are optimized into intervals, so the optimizer can make the
most efficient pass through an index.

http://dev.mysql.com/doc/mysql/en/Range_access_single-part.html

Regards,
Bill K.