Search code examples
mysql

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?

Solution

  • 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.