1

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.

  • 1
    This question is incomplete -- As you mention later, it is cluttered with subqueries, JOINs, or other stuff that may, in fact, be the real villains. To get a real answer to the real question, please provide the real query and SHOW CREATE TABLE for each relevant table. We may even provide a partial answer by suggesting better indexes. – Rick James Jul 12 at 0:40
  • Is there any reason to delay posting your A) real QUERY, B) EXPLAIN SELECT SQL_NO_CACHE (rest of your query); C) SHOW CREATE TABLE for each relevant table; D) SHOW INDEX FROM for each relevant table; for real assistance? – Wilson Hauck Jul 18 at 21:31
  • 1
    @WilsonHauck, purely for simplicity. As you can see, I got a great answer to this question below. – Patrick Geyer Jul 19 at 9:38
  • @PatrickGeyer This would be a GREAT classroom example if you would share Original Query and Solution Query for the practical complete Queries before and after. Thank You for sharing including approximate run times of before and after of 1 and 1,000 actions. – Wilson Hauck Jul 19 at 12:53
  • @PatrickGeyer Very complimentary evidence (if you could share) would be Original Query and Solution Query for each, EXPLAIN SELECT SQL_NO_CACHE (your query). This would document the ROWS retrieved by table and make crystal clear the reason for the improvement. Thank you. – Wilson Hauck Jul 20 at 0:34
2

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 .

  • Hi Camba, thanks for your answer! It is run for multiple records, so I can see why it is causing high CPU usage. My issue with joining instead of subquery (i.e. putting each subquery into it's own table and then joining with the "Account" table) is that I can't scale that. For example if I run the query for just 1 account, it needs to be 100 times faster than running it for 100 accounts. I don't know if that's possible while joining, because all the other tables would need to be computed and only then would the results be filtered with the WHERE clause. Hope that makes sense. Any suggestions? – Patrick Geyer Jul 11 at 17:30
  • Also, I know it's not in the stackexchange spirit, but can offer a $100 bounty to anyone who can help me on getting my CPU usage down on this particular bounty. Let me know if interested @camba :) – Patrick Geyer Jul 11 at 17:41
  • You do not really want query to take 5s for 1 account and 500s for 100. That is linear growth & is not good for scaling up either. Your goal instead should be that it runs in 6s for 1 account & in 8s for 100. Thus, I would be to take the individual bits of you query & optimized them first to run for 1 or 100 accounts. I would then try to combined them using joins or temp table and optimizing that. In the end, if you absolutely need the query for 1 account to be as fast as possible, you could always have two queries , but then you have to keep them in synch. – camba1 Jul 11 at 17:54
  • I appreciate the bounty offer, but I am just here to help either way. Unfortunately, without knowing a lot more details particular to your situation, I am afraid I cannot offer much more than an approach on the way I would address the situation. – camba1 Jul 11 at 17:57
  • Thanks for letting me know - really useful advice! Will give it a go. But if I do use joins, and I have 100 times as many accounts on the system, won't the query take ridiculously long and eat 100% CPU resources while it's running? Whereas if I use the subquery method, I can run in batches of 10 accounts for example? – Patrick Geyer Jul 11 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.