MySQL order of execution

MySQLoptimizationperformancequery-performance

I am running a very long query described below.
It fetches the next needed action for each account on an automation system.

I.e.

SELECT Account.id, 
(IFNULL(**Should send message query**, 
    IFNULL(**Should check inbox**, NULL))) as nextTask FROM Account

In reality the string of IFNULL's is around 10, each is quite a complicated subquery.

I want to know if MySQL will compute the values for following IFNULL expression if the first is satisfied. That is, if an account should send a message, it shouldn't bother computing the subquery for Should check inbox

Is this how MySQL works?

What's the difference between this and CASE WHEN's

E.g.

CASE WHEN **Should send message** THEN **Should send message**
    WHEN **Should check inbox** THEN **Should check inbox**
END

I just want to get the CPU usage down for this query.

Best Answer

Breaking down your example, we have: 

IFNULL(A,B)

This means: Evaluate A, IF A <> Null return A Else Evaluate and return B

CASE WHEN A THEN A
    WHEN B THEN B
END

Which means: Evaluate A if true then evaluate and return A (since we are using A as condition and return expression above). If B then evaluate and return B.

As such, it is best to use IFNULL if the condition we are testing for null is also the return value. Using case is better if you have a condition to test and a different return value.

Having said all that, your first example seems to be using nested IFNULLs. However, just doing IFNULL(**Should send message query**, **Should check inbox**) may be be enough since it just needs to get one or the other or null.

Finally, Are you running this statement for multiple records? If so, that may be the cause of your high CPU as putting sub-selects in the select clause will cause correlated sub-selects to run for each record (i.e if you are processing 1000 entries with your query, each sub select in the query will run 1000 times). At that point I would say to try to re-write the query to first get all the values for inbox and message (maybe two subqueries in the from clause or put them in a temp table) and then do the ifnull at the end. In the end, as usual check the execution plan of your query and see what's going on .

Related Question