We are using MySQL 5.0.44. When I run a stored procedure first time, it might take over a second. If I immediately rerun it, it takes milliseconds. Each subsequent rerun is also super fast.
Why is this happening? Is there some caching?
I thought there is no stored procedure caching in our mysql version.
The reason I’m asking is because I am trying to optimize a stored procedure ‘A’. So I created a modified version ‘B’. I then randomly choose 1000 inputs, and run A on all of them and then run B on all of them. I wonder if B performs better because I just ran A on the same input and something got cached.
Can someone shed a light on what could cause 2nd run of same stored procedure on same input be much faster than first one?
And also, what is the best way to perform performance comparison of 2 different stored procedures?
Thanks a lot!