Member Avatar for |-|x

During some recent project issues I found myself asking this very question: Does MySQL short circuit WHERE clause statements?

After doing some research and finding that several other people and technical forums have posed this question, with several answers pertaining to be intelligent but that amount to "i don't know", I decided to perform some experiments.

Firstly, how to test whether a statement is being executed or not? Solution: make it throw an exception. I initially tried some select statements with parameters and division by zero, but MySQL apparently returns NULL instead of a divide-by-zero error. So I came up with the following statements:

SELECT * FROM tblp
WHERE true OR (SELECT value FROM tblp);

SELECT * FROM tblp
SELECT false OR (SELECT value FROM tblp);

While this is syntactically a legal statement, it is not a logically correct one as the subquery will return multiple rows.

  • The first statement executes successfully and returns all rows in the table, equivalent to select * from tblp where true

  • The second statement, however, throws an error Subquery returns more than 1 row

This to me is pretty definitive evidence that MySQL does short circuit WHERE OR statements.

Edited by |-|x because: typo
  • 2 Contributors
  • 3 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by |-|x
Member Avatar for |-|x

Incidentally, I verified that the inverse holds true for an AND clause

SELECT * FROM tblp
WHERE false AND (SELECT value FROM tblp);

^ Short circuits executing successfully and returns 0 rows.

SELECT * FROM tblp
SELECT true AND (SELECT value FROM tblp);

^ Throws the subquery exception.

Member Avatar for smantscheff

The exception is thrown already in the query analysis. Try to EXPLAIN your queries and you will see that the first EXPLAIN has a result while the second one is already rejected without the query being actually executed. So I'm not sure where the short-circuiting occurs - not in the execution phase, it seems.

Member Avatar for |-|x

It is my understanding that EXPLAIN performs an analysis of the execution, not a pre-analysis as MS SQL Servers optimisation engine does. It simply provides information about the execution path, it doesn't alter the query or optimise it in any way.

If we alter the subquery to eliminate the error, the EXPLAIN results are the same for both queries.

EXPLAIN SELECT * FROM tblp
WHERE true OR (SELECT value FROM tblp LIMIT 1);

In order to demonstrate that MySQL isn't optimising out the hard coded values we can use a variable, which achieves exactly the same results.

set @v = true;

SELECT * FROM tblp
WHERE @v OR (SELECT value FROM tblp);      #  Successful

SELECT * FROM tblp
WHERE not @v OR (SELECT value FROM tblp);  #  Subquery Error

Additionally, if we reverse the order of the OR arguments, it doesn't work.

SELECT * FROM tblp
WHERE (SELECT value FROM tblp) OR true;      #  Subquery Error

Thus, we can conclude that during execution the WHERE clauses are evaluated in order and that MySQL does in fact short circuit once it has a determination of the overall outcome.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.