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

Question

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 1

Purpose of NULLIF and IFNULL is not same, so performance comparison does not make any sense.

NULLIF is used to return null if the expression has a specific value, whereas IFNULL is used to return text if expression is null.

Example:

SELECT IFNULL(field,'empty') from table1;

Since null does not make much sense to end user.

insert into table1 (field) values (nullif(field,'empty'));

Since null has a special meaning in database.

Answer 2

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.

Answer 3

they are used for different purpose, performance comparison doesn't make any sense.

select if(a,b,c);    # a ? b : c       # if a!=0 and a is not null then b else c

select ifnull(a,b);  # a ? a : b       # if a is not null then a else b

select nullif(a,b);  # a=b ? null : a  # if a=b then null else a

http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html