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.

Answer 2

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