In MySQL, which is more efficient: IFNULL or NULLIF?

mysql ifnull null-test
user2259597 picture user2259597 · Apr 9, 2013 · Viewed 17.7k times · Source

These two MySQL functions do the same thing:

IFNULL(column_name, 'test') = 'test'

or 

NULLIF(column_name, 'test') IS NULL

Which one is more efficient?

Answer

Bohemian picture Bohemian · Apr 9, 2013

They are both as efficient as each other - the functions have about the same overhead as each other.

But this is more efficient than either:

(column_name is null 
 or column_name = 'test')

Because the function doesn't need to be invoked.

You may find putting the more commonly encountered test first improves performance.

With questions like this, the simplest and more reliable way to discover relative performance is to just try them and compare query timings. Make sure you have production-sized and realistic-valued datasets so the test is fair.

Related questions

MySQL IFNULL ELSE

I have a select statement where I want to make the select conditional like this: IFNULL(field_a, field_a, field_b) so that it checks field a. If a is null then the select would be field b. Is …

Read More
How does MySQL CONCAT IFNULL work with more than two values?

I need to CONCAT two fields, if they both exist and add parentheses around the second field. Otherwise I just need to list the first field. Here's the simple version: SELECT id, CONCAT(name,' (',nickname,')') as …

Read More
How can i use IFNULL in Where clause

I want to use IFNULL() in such a way that I can select the record containing NULL or, if a value is present, then select the record matchinga particular value. My query is: SELECT (@v:=2) AS Row, CL.LeaveTypeId, CL.…

Read More