CASE or COALESCE performance in WHERE clause for MySQL

Question

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

Answer 1

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)