Morgan Tocker (mtocker) wrote,
Morgan Tocker
mtocker

How fast (or slow) is MySQL Stored Procedure language?

I had a long flight from Sydney to Edinburgh this weekend, and wanted to answer a common training question - how fast/slow is the stored proc language in MySQL. To do this, I started by stealing an example exercise we have in one of our exercises:


DELIMITER //
CREATE FUNCTION fibonacci(n INT)
RETURNS DOUBLE
NO SQL
BEGIN
DECLARE f1, result DOUBLE DEFAULT 0.0;
DECLARE f2 DOUBLE DEFAULT 1.0;
DECLARE cnt INT DEFAULT 1;
WHILE cnt <= n DO
SET result = f1 + f2;
SET f1 = f2;
SET f2 = result;
SET cnt = cnt + 1;
END WHILE;
RETURN result;
END //


If I run this a few times, here are the results:

mysql> select benchmark(100, fibonacci(40000));
+----------------------------------+
| benchmark(100, fibonacci(40000)) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (17.94 sec)


Then if I write a simple PHP script that does the same (without any further optimization)...

..
function fibonacci ($n) {

$f1 = 0.0;
$result = 0.0;
$f2 = 1.0;
$cnt = 1;

while($cnt <= $n) {
$result = $f1 + $f2;
$f1 = $f2;
$f2 = $result;
$cnt = $cnt+1;
}

return $result;
}
..

How long does it take?

$ php fib.php 40000 100
Finding fib 40000, 100 times
Took 1.7208609580994 seconds


Conclusion: 17.94 seconds versus 1.72 seconds, so MySQL is ten times slower!

There's a small amount of overhead added to MySQL because the procedure has to load up/deconstruct 100 times and build a result to return, but by another test I think this only accounts for 0.19 seconds.
Tags: mysql
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 15 comments