Mysql Question posted in
The official MySQL documentation can be found here.

case vs if performance-wise in mysql query

In a query I have a calculated field that is based on several criterias to be matched at the same time.

IF progress = 0
    IF start<now() AND end>now() THEN "delay"
    IF start<now() AND end<now() THEN "overdue"
IF progress BETWEEN 0 AND 1
    IF start<now() THEN "advanced"
    IF end<now() THEN "overdue"
    IF end>now() AND end*progress>=now() THEN "in progress"
    IF end>now() AND end*progress<now() THEN "delayed"
IF progress=1 THEN "completed"

I am looking at how to set this set of conditions performance-wise. Should I change the outer IF (the one about the progress field) to a CASE?

Tags:

2

Answers


  1. 0

    Short Answer: The performance diff between IF and CASE is probably insignificant.

    Long Answer: Well, your code won’t work as it stands, but let me guess at what you intended:

    IF start<now() AND end>now() THEN "delay"
IF start<now() AND end<now() THEN "overdue"

    Assume start is necessarily less than end, change to

    IF (start<now()) THEN
    IF (end>now()) THEN "delay" ELSE IF (end<now()) THEN "overdue"

    or

    IF (start<now()) THEN
    CASE WHEN end>now() THEN "delay"
         WHEN end<now() THEN "overdue"
    END

    end*progress is too kludgy for words. Write out the IF or CASE, then simplify.

    Login or Signup to reply.
  2. 0

    I am looking at how to set this set of conditions performance-wise. Should I change the outer IF (the one about the progress field) to a CASE?

    It doesn’t matter. Any difference will be trivial. MySQL might even implement them exactly the same.

    Since it’s part of the select clause it only affects the already selected rows. The process of reading, filtering, and sorting rows is much, much more expensive. It’s much more important to focus optimization efforts on those portions of your query: where, order by, join, group by, your indexes, and so on.

    Note: end is a SQL reserved word; it’s ambiguous whether you’re referring to the column or ending a block and this can cause hard to debug errors. Consider using the *_at and *_on conventions for timestamps and dates respectively. start_at and end_at for timestamps, start_on and end_on for dates.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search