query optimization - mysql, ifnull vs coalesce, which is faster?

Question

if it's known that there are only two values to candidate for the result of a column,

ifnull(a, b) as a_or_b_1

and

coalesce(a, b) as a_or_b_2

will give the same result. but which is faster? when searching i found this article, which says ifnull is faster. but it was the only article i found. any views on this?

thanks in advance :)

Answer 1

My view is that you should benchmark for your usage.

I doubt there will be much difference. Bear in mind that while a single benchmark might suggest that one is slightly better, variation in the data over time might change that result.

Also note that COALESCE has been part of standard SQL since 1992 - I'm not sure IFNULL is in any standard yet.

There's a nice article by Adam Machanic about benchmarking an equivalent scenario - Performance: ISNULL vs. COALESCE (in SQL Server). Note some of the provisos on getting a valid test.