Bug #91585 “dead” code inside the stored proc or function can significantly slow it down
Submitted: 10 Jul 1:16 Modified: 10 Jul 9:39
Reporter: Alexander Rubin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S5 (Performance)
Version:5.7, 8.0, 8.0.11, 5.7.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance, stored function, stored procedure, trigger

[10 Jul 1:16] Alexander Rubin
Description:
 “dead” code (the code in a branch which will never run) can still significantly slow down the response time of a function/procedure/trigger.

A stored procedure with 4 if statements which will never be true (so the code inside will never execute, "dead" code) will run 2x slower compared to the stored procedure with no if statement. According to the (attached) ps_trace result it actually run sql/THD::LOCK_thd_data and opening_tables for each sp/jump_if_not

How to repeat:
Create 3 functions (it does not matter what will be executed inside or if it exist or not):

Function 1:

CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11)
BEGIN
	declare r int default 0;
RETURN r;
END

Function 2:

CREATE DEFINER=`root`@`localhost` FUNCTION `func2`() RETURNS int(11)
BEGIN
    declare r int default 0;
    IF 1=2
    THEN
		select levenshtein_limit_n('test finc', 'test func', 1000) into r;
    END IF;
RETURN r;
END

Function 3:

CREATE DEFINER=`root`@`localhost` FUNCTION `func3`() RETURNS int(11)
BEGIN
    declare r int default 0;
    IF 1=2 THEN
		select levenshtein_limit_n('test finc', 'test func', 1) into r;
    END IF;
    IF 2=3 THEN
		select levenshtein_limit_n('test finc', 'test func', 10) into r;
    END IF;
    IF 3=4 THEN
		select levenshtein_limit_n('test finc', 'test func', 100) into r;
    END IF;
    IF 4=5 THEN
		select levenshtein_limit_n('test finc', 'test func', 1000) into r;
    END IF;
RETURN r;
END

Run benchmark:

mysql> select benchmark(1000000, func1());select benchmark(1000000, func2());select benchmark(1000000, func3());
+-----------------------------+
| benchmark(1000000, func1()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (2.48 sec)

+-----------------------------+
| benchmark(1000000, func2()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (3.53 sec)

+-----------------------------+
| benchmark(1000000, func3()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (5.69 sec)
[10 Jul 1:18] Alexander Rubin
Func1,a trace with ps_trace_thread() procedure

Attachment: func1.png (image/png, text), 333.83 KiB.

[10 Jul 1:18] Alexander Rubin
Func2, a trace with ps_trace_thread() procedure

Attachment: func2.png (image/png, text), 333.00 KiB.

[10 Jul 1:18] Alexander Rubin
Func3, a trace with ps_trace_thread() procedure

Attachment: func3.png (image/png, text), 460.01 KiB.

[10 Jul 9:39] Umesh Shastry
Hello Alexander,

Thank you for the report.

Thanks,
Umesh