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?

@Rick James -- thanks, it was a typo when posting the question. In actuality it was correctly formatted to '20230101' or '2023-01-01'; I'll edit the post. — abawb
– abawb, Commented Mar 3, 2024 at 23:01
Now we need to see the query. Better yet, let's see the Stored Proc. — Rick James
– Rick James, Commented Mar 4, 2024 at 2:52

Rick James · Accepted Answer · 2024-03-03 19:37:40Z

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

answered Mar 3, 2024 at 19:37

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wilson Hauck Over a year ago

Need to see the query, stored procedure and TEXT results of SHOW CREATE TABLE tbl_name' for each table involved, please.

Collectives™ on Stack Overflow

Mysql Stored Procedure performance when using user-defined variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related