Flite Careers

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

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
mysql> delimiter $$
mysql> drop function if exists my_concat $$
Query OK, 0 rows affected (0.01 sec)
mysql> create function my_concat (p_str1 longtext, p_str2 longtext) returns longtext
    -> begin 
    ->   return concat(p_str1,p_str2);
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> select benchmark(10000000,concat('foo','bar'));
+-----------------------------------------+
| benchmark(10000000,concat('foo','bar')) |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.44 sec)

mysql> select benchmark(10000000,my_concat('foo','bar'));
+--------------------------------------------+
| benchmark(10000000,my_concat('foo','bar')) |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (50.30 sec)

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.

UDFs

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.

Comments

Comments