Morgan Tocker (mtocker ) wrote,

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
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 15 comments