That's what I was missing (I'm tired and sick :( ). I think you're right, Steve. Thanks.
On Dec 23, 2011, at 18:45, Steve Meyers <[email protected]> wrote: > On 12/23/11 6:48 PM, Wade Preston Shearer wrote: >> Am I correct in my understanding that the right side of an OR >> statement in an MySQL query doesn't get evaluated unless the left >> side fails? I have a query where the left-side case of an OR occurs >> often and produces an efficient query and the right-side happens only >> occasionally and produces a slower, less efficient query. If the >> query always checks both but only uses the left if it is true, then >> I'd be better served to move it into two queries. If it only checks >> the right-side condition if the left-side fails though, then keeping >> it in one makes sense. > > I think you're thinking about this backwards. You're thinking of it as used > in a programming language, when you're evaluating a single value. That is not > the case here. MySQL is evaluating all of the rows, and deciding which ones > match. In evaluating a single row, it would make sense for it to only > evaluate the left side. However, database optimization generally comes from > using indexes, not from optimizing the database server's look at a single > row. In other words, the real optimization comes from limiting the number of > rows looked at rather than limiting what is done evaluating each row. The > latter is straightforward. The former is much harder. > > If your query contains "WHERE (A) OR (B)", and (A) can be optimized with an > index, how does that help? You still need to evaluate every other row to > determine if (B) is true. It'd actually be slower to use the index than to > avoid it. > > Now, if (A) and (B) both use an index, then an index merge is possible. Most > versions of MySQL do not support that very well. I know that Monty has done > some work to make index merges work well in MariaDB, at least in 5.3. > > Steve _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
