The BENCHMARK SQL Function in MySQL and MariaDB - Measure speed of expressions |
|
| | BENCHMARK | Syntax: | BENCHMARK(Number, Expression) | | Return value: | INTEGER | | Function type: | Information function | |
| | The BENCHMARK() function executes the "Expression" repeatedly "Number" times.
It can be used to measure how fast the database is processing the expression.
The function is not suitable for measuring how fast queries from tables are because the query optimizer falsifies the result.
The result value is 0. If count is negative, the BENCHMARK() function returns NULL.
The function should be used with caution, as executing time- or resource-intensive expressions can adversely affect the overall performance of the server. | Examples of the BENCHMARK() function with MariaDB 10.3 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
| set @time=sysdate();
SELECT benchmark(100000000, adddate(now(), interval 1 second));
select timediff(sysdate(),@time);
set @time=sysdate();
SELECT benchmark(100000000, (select adddate(now(), interval 1 second) as n from menu limit 1));
select timediff(sysdate(),@time);
set @time=sysdate();
SELECT benchmark(100000000, (select count(*) from menu));
select timediff(sysdate(),@time);
set @time=sysdate();
SELECT benchmark(100000000, (select sum(pos) from menu));
select timediff(sysdate(),@time);
SELECT benchmark(-10000, (select sum(pos) from menu));
|
|
| benchmark(100000000, adddate(now(), interval 1 second)) |
|---|
| int(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(10) |
|---|
| 00:00:06 |
|
|
| benchmark(100000000, (select adddate(now(), interval 1 second) as n from menu limit 1)) |
|---|
| int(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(10) |
|---|
| 00:00:03 |
|
|
| benchmark(100000000, (select count(*) from menu)) |
|---|
| int(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(10) |
|---|
| 00:00:01 |
|
|
| benchmark(100000000, (select sum(pos) from menu)) |
|---|
| int(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(10) |
|---|
| 00:00:00 |
|
|
| benchmark(-10000, (select sum(pos) from menu)) |
|---|
| int(1) |
|---|
| NULL |
|
|
| Examples of then BENCHMARK() Function with MySQL 8.0 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
| set @time=sysdate();
SELECT benchmark(100000000, adddate(now(), interval 1 second));
select timediff(sysdate(),@time);
set @time=sysdate();
SELECT benchmark(100000000, (select adddate(now(), interval 1 second) as n from menu limit 1));
select timediff(sysdate(),@time);
set @time=sysdate();
SELECT benchmark(100000000, (select count(*) from menu));
select timediff(sysdate(),@time);
set @time=sysdate();
SELECT benchmark(100000000, (select sum(pos) from menu));
select timediff(sysdate(),@time);
SELECT benchmark(-10000, (select sum(pos) from menu));
|
|
| benchmark(100000000, adddate(now(), interval 1 second)) |
|---|
| bigint(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(17) |
|---|
| 00:00:21.000000 |
|
|
| benchmark(100000000, (select adddate(now(), interval 1 second) as n from menu limit 1)) |
|---|
| bigint(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(17) |
|---|
| 00:00:01.000000 |
|
|
| benchmark(100000000, (select count(*) from menu)) |
|---|
| bigint(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(17) |
|---|
| 00:00:02.000000 |
|
|
| benchmark(100000000, (select sum(pos) from menu)) |
|---|
| bigint(1) |
|---|
| 0 |
|
|
| timediff(sysdate(),@time) |
|---|
| time(17) |
|---|
| 00:00:02.000000 |
|
|
| benchmark(-10000, (select sum(pos) from menu)) |
|---|
| bigint(1) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the BENCHMARK() function in MySQL and MariaDB databases | In MySQL and MariaDB the BENCHMARK() function is used to evaluate a specific expression or function to run repeatedly for a specified number of iterations. The function is mainly used for benchmarking and performance testing purposes, where the execution time of a given expression or function can be evaluated to compare different approaches or measure performance improvements. The expression to be measured can be any valid SQL expression or any valid SQL function.
The BENCHMARK() function returns the result of the last iteration of the expression or function, however the actual result may not be meaningful even in benchmarking scenarios since its main purpose is to measure execution time. Using the function can impact server performance when performing complex or resource-intensive operations for a large number of iterations. | | Further MySQL und MariaDB SQL Information functions | |
| | | More information about the BENCHMARK SQL function: and |
|
|
|
|
