Description:
sp_head.cc has comment like below:
TODO: we should create sp_rcontext once per command and reuse
it on subsequent executions of a function/trigger.
According to CPU profiling, functions may spend up to ~25% of time in creating sp_rcontext in 5.6 and 5.7 :
+ 84.88% 2.38% 928 mysqld mysqld [.] sp_head::execute_function + 44.44% 4.08% 1590 mysqld mysqld [.] sp_head::execute + 22.32% 0.30% 118 mysqld mysqld [.] sp_rcontext::create
The same profiling on 5.1 shows only 1,75% spent in sp_rcontext::create :
+ 1.79% 1.79% 587 mysqld mysqld [.] sp_rcontext::init
In particular, handling of 200M MyISAM table with two simple functions takes 2h in 5.6 and only 1h in 5.1 . Without these functions execution time is approximately the same. This means sp_rcontext::create is unnecessary called ~400M times in that case, which should be around 160K / sec, which definitely influences execution time.
How to repeat:
See source code or profile following using any CPU profiler in 5.1 vs 5.6 / 5.7 :
CREATE FUNCTION f1(a int) returns bool DETERMINISTIC return true;
do benchmark(50000000, f1(1));
Suggested fix:
Reuse sp_rcontext when processing many rows with stored function / triggers