In this article, I’m going to cover the use of built-in functions. The idea is to use the functions that MySQL has available instead of making custom functions (aka user-defined functions). If they do the same thing, why use MySQL functions instead of my own custom made functions?
The answer is simple: Performance!
Built-in functions are written directly in C which is tremendously fast, while the SQL-written functions will have to go through interpreter and that will make them inevitably slower (like comparing a program written in -say- PHP or Python vs one written in C; Even with pre-compiled bytecode the scripting languages will always be slower as the bytecode still has to go through an execution VM)
Here’s a mini benchmark running 5,000,000 times POW(N, 2) and a custom function that calculates power-of-2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql [localhost:5724] {msandbox} (test) > CREATE FUNCTION power_of_two(base FLOAT) RETURNS FLOAT DETERMINISTIC -> BEGIN -> RETURN base*base; -> END -> -> // Query OK, 0 rows affected (0.03 sec) mysql [localhost:5724] {msandbox} (test) > DELIMITER ; mysql [localhost:5724] {msandbox} (test) > SELECT BENCHMARK(5000000, POW(1.4142135623730951, 2)); +------------------------------------------------+ | BENCHMARK(5000000, POW(1.4142135623730951, 2)) | +------------------------------------------------+ | 0 | +------------------------------------------------+ 1 row in set (3.64 sec) mysql [localhost:5724] {msandbox} (test) > SELECT BENCHMARK(5000000, power_of_two(1.4142135623730951)); +------------------------------------------------------+ | BENCHMARK(5000000, power_of_two(1.4142135623730951)) | +------------------------------------------------------+ | 0 | +------------------------------------------------------+ 1 row in set (38.82 sec) |
It is possible to see that the difference is 10x for the simplest of functions (multiply 2 numbers), and this is due to all the overhead of SQL functions.
That’s it! I hope that with this short article you got a better understanding of the performance impact that can be avoided using built-in functions.
See you next!
Nice one Vinnie !