CODEWITHSUNDEEP
mysqlsqlperformancenullcase
Mike Flynn
Mike Flynn

Reputation: 24325

CASE or COALESCE performance in WHERE clause for MySQL

I'm wondering which is the better performance or best practice when dealing with multiple criteria in the WHERE clause and NULL values. 

WHERE
    u.id = COALESCE(user_id, u.id) AND
    su.custom_id = COALESCE(student_number, su.custom_id)

OR

WHERE
CASE 
    WHEN user_id IS NOT NULL AND LENGTH(user_id) > 0
    THEN
        u.id = user_id 
    ELSE
        su.custom_id = student_number
    END

Upvotes: 9

Views: 23806

Answers (2)

Ike Walker
Ike Walker

Reputation: 65567

I would avoid both of those approaches. You should be able to accomplish what you are trying to do with a judicious use of AND, OR, IS NULL, and parentheses.

For example, you can rewrite this:

WHERE
    u.id = COALESCE(user_id, u.id) AND
    su.custom_id = COALESCE(student_number, su.custom_id)

Like this:

WHERE
    (user_id IS NULL OR u.id = user_id) AND
    (su.custom_id = student_number)

And you can rewrite this:

WHERE
CASE 
    WHEN user_id IS NOT NULL AND LENGTH(user_id) > 0
    THEN
        u.id = user_id 
    ELSE
        su.custom_id = student_number
    END

As this:

WHERE
(user_id IS NOT NULL AND LENGTH(user_id) > 0 AND u.id = user_id)
OR
(su.custom_id = student_number)

Upvotes: 16

Rahul
Rahul

Reputation: 77896

I would suggest using explain and seehow your query plan is. Like:

explain <your select query here>

Upvotes: 0

Related Questions