Mysql Stored Procedure performance when using user-defined variable

Question

I have a mysql 8.0 stored procedure which uses a start_date parameter in the logic.

If start_date is "set" (e.g., set @start_date = '2023-01-01', the query executes in a fraction of a second.

However, if I declare a local variable (i.e., declare start_date date default '2023-01-01', or if I hard-code the start_date , the query takes almost 350 seconds.

Why might there be such a dramatic difference in performance? The output is correct and as expected in both cases -- but one just takes an enormous amount of time.

Below is the EXPLAIN output when "setting" the variable:

and the output when "declaring" the local variable is below. Why is the row count so dramatically different?

Answer 1

select date('2023-0-01') is null; --> 1

That is, your string is treated as a NULL.

Nulls often work in surprising ways.

If you like help in optimizing the query, let's see it. (The EXPLAIN is not enough.)