mysqlifnullnull-test

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

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?

Solution

  • 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.