The Difference Between UDFs and Stored Functions in MySQL
I’ve seen some confusion about the difference between user-defined functions (UDFs) and stored functions in MySQL. In my last post I talked about UDFs, so I figured now is a good time to write about the difference between the two types of functions you can create in MySQL. Both are created using
CREATE FUNCTION, and they share the same namespace, but they are implemented completely differently.
Stored functions are simply stored routines that return a value. Stored functions are easy to create if you know SQL, and they can be invoked just like built-in functions, but they are much slower than built-in functions. To illustrate that point, look at how re-implementing the built-in
CONCAT() function as a stored function makes it more than 100 times slower:
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
Running the built-in
CONCAT() 10 million times took less than half a second, and running the stored function version 10 million times took over 50 seconds.
User-defined functions are written in C or C++. The process is well-documented in the manual. Since they are written in C/C++ they are not as easy to implement for many SQL developers, but they are much faster than stored functions. MySQL source distributions include a file called udf_example.c that defines some example UDFs.