In MariaDB, BENCHMARK() is a built-in function that executes an expression a specified number of times.

It can be used to time how quickly MariaDB processes the expression.

The result is always 0. The function is intended to be used within the mysql command line tool, which reports query execution times.

Syntax

The syntax goes like this:

BENCHMARK(count,expr)

Where count is the number of times you want expr to run.

Example 1

Here’s an example to demonstrate:

SELECT BENCHMARK(2, SLEEP(2));

Result:

+------------------------+
| BENCHMARK(2, SLEEP(2)) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (4.015 sec)

As expected, 0 was returned. However, what we’re interested in is the query execution time.

Here I used the SLEEP() function to sleep for 2 seconds. Using this function by itself would normally cause the statement to take around 2 seconds to execute.

However, in this case I passed 2 as the first argument to BENCHMARK(). That caused the SLEEP() function to execute twice, causing the BENCHMARK() function to take around 4 seconds to execute (4.015 seconds to be precise).

Here’s what happens if we increase the first argument to 3:

SELECT BENCHMARK(3, SLEEP(2));

Result:

+------------------------+
| BENCHMARK(3, SLEEP(2)) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (6.012 sec)

This time the SLEEP() function was executed three times, and so BENCHMARK() took just over 6 seconds to execute.

Example 2

Here’s an example that uses the ENCODE() function to encode a string (first argument) with a password (second argument):

SELECT BENCHMARK(100000, ENCODE('Homer', 'Symptom'));

Result:

+-----------------------------------------------+
| BENCHMARK(100000, ENCODE('Homer', 'Symptom')) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.016 sec)

We can see that the operation took 0.016 seconds to complete.

Let’s increase the first argument from 100,000 to 1,000,000 and see how that affects the execution time.

SELECT BENCHMARK(1000000, ENCODE('Homer', 'Symptom'));

Result:

+------------------------------------------------+
| BENCHMARK(1000000, ENCODE('Homer', 'Symptom')) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.118 sec)

This time it took much longer to run.

Null Argument

If the first argument is null, then null is returned:

SELECT BENCHMARK(null, ENCODE('Homer', 'Symptom'));

Result:

+---------------------------------------------+
| BENCHMARK(null, ENCODE('Homer', 'Symptom')) |
+---------------------------------------------+
|                                        NULL |
+---------------------------------------------+
1 row in set (0.007 sec)

But if only the second argument is null, then 0 is returned:

SELECT BENCHMARK(100000, null);

Result:

+-------------------------+
| BENCHMARK(100000, null) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.002 sec)

Invalid Number of Arguments

Calling BENCHMARK() with no arguments, or with the wrong number of arguments, results in an error:

SELECT BENCHMARK();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'BENCHMARK'

And:

SELECT BENCHMARK(1, 2, 3);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'BENCHMARK'