How the BENCHMARK() function works in Mariadb?

The BENCHMARK() function is a utility function that measures the execution time of a given expression. The BENCHMARK() function can be used to test the performance of a function, a query, or a statement.

Posted on

The BENCHMARK() function is a utility function that measures the execution time of a given expression. The BENCHMARK() function can be used to test the performance of a function, a query, or a statement. For example, the BENCHMARK() function can be used to compare the speed of different hashing algorithms.

Syntax

The syntax of the BENCHMARK() function is as follows:

BENCHMARK(count, expression)

The count parameter is an integer value that specifies the number of times to execute the expression. The count parameter cannot be NULL or negative.

The expression parameter is any valid expression, such as a function call, a query, or a statement. The expression parameter is the expression that you want to measure the execution time of. The expression parameter cannot be NULL.

The BENCHMARK() function returns 0 as the output, which is a dummy value. The actual result of the BENCHMARK() function is the execution time of the expression, which is displayed in the server output or the log file.

Examples

Here are some examples of using the BENCHMARK() function in Mariadb:

Example 1: Execution time of a function call

To get the execution time of a function call, you can use the function call as the expression parameter. For example, you can use the BENCHMARK() function to measure the execution time of the MD5() function, which returns the MD5 hash of a given string. For example:

SELECT BENCHMARK(1000000, MD5('Hello')) AS result;

This statement returns 0 as the output, which is a dummy value. The actual result of the BENCHMARK() function is the execution time of the MD5() function, which is displayed in the server output or the log file. For example, the server output may show something like this:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.28 sec)

This means that the MD5() function took 0.28 seconds to execute 1000000 times.

Example 2: Execution time of a query

To get the execution time of a query, you can use the query as the expression parameter. For example, you can use the BENCHMARK() function to measure the execution time of a SELECT query, which returns the rows from a table. For example, suppose you have a table called products that stores the name and the price of different products. You can use the BENCHMARK() function to measure the execution time of a SELECT query that returns all the products with a price greater than 1. For example:

SELECT BENCHMARK(1000, (SELECT * FROM products WHERE price > 1)) AS result;

This statement returns 0 as the output, which is a dummy value. The actual result of the BENCHMARK() function is the execution time of the SELECT query, which is displayed in the server output or the log file. For example, the server output may show something like this:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.01 sec)

This means that the SELECT query took 0.01 seconds to execute 1000 times.

Example 3: Execution time of a statement

To get the execution time of a statement, you can use the statement as the expression parameter. For example, you can use the BENCHMARK() function to measure the execution time of an INSERT statement, which inserts a new row into a table. For example, suppose you have a table called numbers that stores a single column of numeric values. You can use the BENCHMARK() function to measure the execution time of an INSERT statement that inserts the value 1 into the table. For example:

SELECT BENCHMARK(10000, (INSERT INTO numbers VALUES (1))) AS result;

This statement returns 0 as the output, which is a dummy value. The actual result of the BENCHMARK() function is the execution time of the INSERT statement, which is displayed in the server output or the log file. For example, the server output may show something like this:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.05 sec)

This means that the INSERT statement took 0.05 seconds to execute 10000 times.

Some of the functions that are related to the BENCHMARK() function are:

  • The SLEEP() function, which pauses the execution for a given number of seconds. The SLEEP() function can be used to simulate a delay or a wait in a function, a query, or a statement. For example, SLEEP(1) pauses the execution for 1 second.
  • The NOW() function, which returns the current date and time. The NOW() function can be used to get the start and end time of a function, a query, or a statement. For example, NOW() returns the current date and time, such as ‘2023-04-15 12:34:56’.
  • The TIMESTAMPDIFF() function, which returns the difference between two timestamps. The TIMESTAMPDIFF() function can be used to calculate the elapsed time of a function, a query, or a statement. For example, TIMESTAMPDIFF(SECOND, start_time, end_time) returns the number of seconds between the start time and the end time.

Conclusion

The BENCHMARK() function is a useful function that can help you measure the execution time of a given expression in Mariadb. You can use it with any valid expression, such as a function call, a query, or a statement. The BENCHMARK() function returns 0 as the output, which is a dummy value. The actual result of the BENCHMARK() function is the execution time of the expression, which is displayed in the server output or the log file. You can also use some other functions that are related to the BENCHMARK() function, such as the SLEEP(), NOW(), and TIMESTAMPDIFF() functions.