0

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.

6
  • Try looking at the EXPLAIN {query} for both forms of this query. I suspect the optimiser doesn't try significantly to unwrap case statements. OR statements limit range of optimizations significantly too. Show MySQL version and what indexes foo has too.
    – danblack
    Mar 7, 2019 at 2:30
  • generated columsn with indexes with an expression col varchar(255) as (IF(column_index=1, column_1, IF(column_index IS NULL, NULL,column_2))) then your query becomes WHERE col IS NULL OR col = value and I think there is a IS NULL OR optimization (could be wrong).
    – danblack
    Mar 7, 2019 at 2:42
  • @danblack The column_selector seems to be the constant/parameter external to the table or the field of another table. If so, no way to use it in generated column.
    – Akina
    Mar 7, 2019 at 5:06
  • I forgot for a moment that an index is a different thing, my bad. Sorry. Thank you all for your time. Mar 8, 2019 at 17:10
  • Suggest you re-think the schema. If you would like to provide the rationale for this discussion, maybe we can help you devise tables that avoid the need for this procedure.
    – Rick James
    Mar 12, 2019 at 15:23

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.