mysql, ifnull vs coalesce, which is faster?

mysql query-optimization
davogotland picture davogotland · Jan 20, 2011 · Viewed 16.9k times · Source

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

martin clayton picture martin clayton · Jan 20, 2011

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.

Related questions

mysql select from n last rows

I have a table with index (autoincrement) and integer value. The table is millions of rows long. How can I search if a certain number appear in the last n rows of the table most efficiently?

Read More
JOIN queries vs multiple queries

Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query) I'm asking because JOINing them would complicate A LOT the design of my …

Read More
How to do the Recursive SELECT query in MySQL?

I got a following table: col1 | col2 | col3 -----+------+------- 1 | a | 5 5 | d | 3 3 | k | 7 6 | o | 2 2 | 0 | 8 If a user searches for "1", the program will look at the col1 that has "1" then it will get a value in col3 "5", then the program …

Read More