Real Performance of Stored Procedure Testing

MySQL & MariaDB Other MySQL® Questions
1

Hello,

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!

2

Each connection has its own stored procedure execution plan path. So subsequent runs of a procedure using the same connection will be much faster. If your application is using connection pooling, you can see the benefits from this.