MySQL built-in function x user-defined function performance

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:

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!

1 Comment

  1. cyberjots October 25, 2019 at 7:13 am

    Nice one Vinnie !


Leave a Reply