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 :)

Uhm, are you sure it is the **slowest** part of your queries? :-) — zerkms, Jan 20 '11 at 13:53

martin clayton · Answer 1 · 2019-12-20T12:11:09.533

19

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.

edited Dec 20 '19 at 12:11

answered Jan 20 '11 at 14:11

martin clayton

76,436
32
213
198

link is down - any other hostings? – Max Dec 19 '19 at 20:28
Hi Max - thanks! It moved sometime ago, updated the link – martin clayton Dec 20 '19 at 12:11

score 4 · Accepted Answer · answered Feb 09 '22 at 19:04

Since an answer was never given for MySQL, here's a custom procedure that shows IFNULL is about 10% slower than COALESCE for larger datasets.

CREATE PROCEDURE `compare_ifnull_coalesce`(max_var INT)
BEGIN
    DECLARE COALESCE_BOTH_NOT_NULL INT(11);
    DECLARE IFNULL_BOTH_NOT_NULL INT(11);
    DECLARE COALESCE_FIRST_NULL INT(11);
    DECLARE IFNULL_FIRST_NULL INT(11);
    DECLARE i INT DEFAULT 1;
    DECLARE StartDate DATETIME DEFAULT SYSDATE(3);
    WHILE i <= max_var DO
        IF (SELECT COALESCE('a', 'b') = 'b') THEN
            SELECT 1;
        END IF;
        SET i = i + 1;
    END WHILE;
    SET COALESCE_BOTH_NOT_NULL = FLOOR(TIMESTAMPDIFF(MICROSECOND, StartDate, SYSDATE(3))/1000);
    SET i = 1;
    SET StartDate = SYSDATE(3);
    WHILE i <= max_var DO
        IF (SELECT IFNULL('a', 'b') = 'b') THEN
            SELECT 1;
        END IF;
        SET i = i + 1;
    END WHILE;
    SET IFNULL_BOTH_NOT_NULL = FLOOR(TIMESTAMPDIFF(MICROSECOND, StartDate, SYSDATE(3))/1000);
    SET i = 1;
    SET StartDate = SYSDATE(3);
    WHILE i <= max_var DO
        IF (SELECT COALESCE(null, 'b') = 'a') THEN
            SELECT 1;
        END IF;
        SET i = i + 1;
    END WHILE;
    SET COALESCE_FIRST_NULL = FLOOR(TIMESTAMPDIFF(MICROSECOND, StartDate, SYSDATE(3))/1000);
    SET i = 1;
    SET StartDate = SYSDATE(3);
    WHILE i <= max_var DO
        IF (SELECT COALESCE(null, 'b') = 'a') THEN
            SELECT 1;
        END IF;
        SET i = i + 1;
    END WHILE;
    SET IFNULL_FIRST_NULL = FLOOR(TIMESTAMPDIFF(MICROSECOND, StartDate, SYSDATE(3))/1000);
    SELECT
        'both columns not null' AS `SCENARIO`,
        CONCAT('Total milliseconds: ', COALESCE_BOTH_NOT_NULL) AS `COALESCE`,
        CONCAT('Total milliseconds: ', IFNULL_BOTH_NOT_NULL) AS `IFNULL`
    UNION
    SELECT
        'first column null' AS `SCENARIO`,
        CONCAT('Total milliseconds: ', COALESCE_FIRST_NULL) AS `COALESCE`,
        CONCAT('Total milliseconds: ', IFNULL_FIRST_NULL) AS `IFNULL`
    ;
END;

Then, to get the results, just run:

CALL compare_ifnull_coalesce(1000000);

SCENARIO	COALESCE	IFNULL
both columns not null	Total milliseconds: 5175	Total milliseconds: 5687
first column null	Total milliseconds: 5185	Total milliseconds: 5793

Results from MariaDB 10.8.3 running in a Podman container on my Linux laptop - slightly different results: both columns not null: COALESCE: 8824 vs IFNULL: 8576. But: COALESCE: first column null: 8363 vs IFNULL: 8811. Results from MySQL 5.7.39 running in a Podman container on my Linux laptop: both columns not null: COALESCE: 9613 vs IFNULL: 8673. first column null: COALESCE: 9076 vs IFNULL 9363. So from these tests it seems that IFNULL can be faster in the "both columns not null" scenario. — dbdemon, Feb 10 '23 at 17:20

mysql, ifnull vs coalesce, which is faster?

2 Answers2

Linked