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
