Is MySQL logic evaluation lazy/short-circuiting in JOIN clause?

Question

Take the following expression: FALSE AND (expression)

Will MySQL evaluate the expression or just move on as soon as it sees FALSE?

Some background context-- I wanted to speed up a query by doing:

JOIN... ON (indexed_column1=indexed_column2 AND non_indexed_column_a=non_indexed_column_b)

For background on why I'm doing this query see this answer

If it's going to always evaluate non_indexed_column_a=non_indexed_column_b then no time is saved with that.

I would think the query optimizer would eliminate this trivial case entirely. However, in cases that can't be statically eliminated, such engines, such as SQL Server (granted, not MySQL) **do not guarantee an evaluation order**; as this actually helps them be more efficient with indices and such. It would be interesting to know how the MySQL-specific implementation works so, +1. — , May 25 '12 at 00:31
http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated (not *specific* to MySQL, but good never-the-less) , http://stackoverflow.com/questions/4449105/mysql-and-condition — , May 25 '12 at 00:33
Hmm, I like the answer in the first linked question too much. Voting to close after all. (Although it might be subtly different if "JOIN" and not "WHERE", as edited.) — , May 25 '12 at 00:35
see manual: http://dev.mysql.com/doc/internals/en/optimizer-eliminating-dead-code.html — Omesh, Aug 30 '12 at 07:21

score 3 · Accepted Answer · answered Sep 05 '12 at 07:27

The MySQL query optimizer uses indexes whenever possible and to use the most restrictive index in order to eliminate as many rows as possible.

So in case of your query it will always filter the records based on first indexes columns and then filter the records from non-index columns.

Also before query execution, MySQL eliminates the code which is always going to be false (Dead Code) .

1 Answers1