Mysql – CASE in WHERE clause in MySQL

caseMySQLperformancestored-procedureswhere

I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:

DELIMITER $$
CREATE PROCEDURE `case_in_where`(IN `column_selector` INT, IN `value` VARCHAR(255)) 
    BEGIN

    SELECT * FROM `foo`
    WHERE 
        CASE
            WHEN `column_selector` IS NULL THEN 1 
            WHEN `column_selector` = 1 THEN `foo`.`column_1` = `value`
            WHEN `column_selector` = 2 THEN `foo`.`column_2` = `value`
        END
    ;
END $$
DELIMITER ;

But I have seen that when people ask for help using this approach, usually the answers are that it should use AND, OR instead or Dynamic SQL. So, this way is not correct? It's a bad practice or have any difference in performance?

I should use AND, OR or Dynamic SQL or make different queries for every case?

Thanks beforehand.

Best Answer

usually the answers are that it should use AND, OR instead or Dynamic SQL.

There is no dynamic SQL in your code.

any difference in performance?

Of course the construction which you show causes fullscan and cannot be optimized. But the condition built using logical operators cannot be optimized normally too. To achieve highest performance use UNION ALL with 3 separate subqueries, one condition per subquery. Because constant-evaluated condition for two of them will give false only one of 3 subqueries will be executed in practice.

In SP this can be 3 separate queries wrapped with external CASE:

DELIMITER $$;
CREATE PROCEDURE `case_in_where`(IN `column_selector` INT, IN `value` VARCHAR(255)) 
    BEGIN
    CASE WHEN `column_selector` IS NULL THEN 
             SELECT * FROM `foo`;
         WHEN `column_selector` = 1 THEN 
             SELECT * FROM `foo` WHERE `foo`.`column_1` = `value`;
         WHEN `column_selector` = 2 THEN 
             SELECT * FROM `foo` WHERE `foo`.`column_2` = `value`;
    END CASE;
END $$;
DELIMITER ;

Flow Control Statements - CASE Syntax

Related Question