CASE or COALESCE performance in WHERE clause for MySQL

Question

I am wondering which one is the best performance or best practice when dealing with multiple criteria in WHERE clauses 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 these approaches. You must accomplish what you are trying to do, with a reasonable 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 

Like this:

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

Answer 2

I would suggest using explain and see what your query plan looks like. How:

 explain <your select query here>