MySQL Query to check at least one field is greater than zero

Question

I want to retrieve the row if any of the 3 integer field in a row is greater than zero. All the field has default value of zero.

Which of the following query is better?

-- Query 1
SELECT * FROM my_table WHERE number_A > 0 OR number_B > 0 OR number_C > 0
-- Query 2
SELECT * FROM my_table WHERE (number_A + number_B + number_C) > 0

While checking the execution time both the query gives same numbers and the result data of the query is exactly the same.

• Billal BEGUERADJ: Generating a query plan for each query will help you to decide. Otherwise, the first query is better in that the SGBD does not need to check the rest of the parameters if number_A satisfies the condition; while in the second query, the SGBD will have to check the values of the 3 parameters and sum them in all cases.
• dnoeth: A 3rd way to write this (similar to Q1, but a bit shorter): WHERE (number_A, number_B, number_C) > (0, 0, 0)

Answer 1

In addition to the other differences between the two statements

WHERE (number_A + number_B + number_C) > 0

Could cause you problems if any of those three are negative. That may not be relevant in your case, but if one of these is negative the total might fail to exceed zero even if another of the values does exceed zero.

Answer 2

A good thing to do would be to use EXPLAIN on both of these statements to see how MySQL executes each of them. They may match identically. The reason for this is that SQL is a declarative programming language, meaning that your query is meant to express the logic of a computation, not it's control. MySQL then takes your query and it's optimizer determines the best / most efficient means of returning the results as defined by your query. Therefore, it can interpret your queries as identical, and execute them as such.