Menu

MariaDB BENCHMARK(): Measure SQL Expression Execution Time

Learn BENCHMARK(count, expr) syntax, why its result is 0, how to compare elapsed times correctly, and when to use EXPLAIN instead.

Posted on By Updated on
On this page

MariaDB BENCHMARK() evaluates a scalar SQL expression repeatedly. It is useful for comparing the execution time of expressions or functions, but it does not measure the complete performance of a table query.

Syntax and return value

BENCHMARK(count, expr)
  • count: The number of repetitions. Start with a modest value and increase it only when needed.
  • expr: A scalar expression, such as a calculation or a string function.

For a normal call with valid arguments, BENCHMARK() returns 0. That number is not elapsed time and is not a performance score. The useful measurement is the time reported by the MariaDB command-line client after executing the statement.

Example: measure an expression

Run this statement in the mariadb command-line client:

SELECT BENCHMARK(100000, MD5('test'));

The resulting SQL value is 0. The command-line client also displays how long the whole statement took, for example in its 1 row in set (... sec) line. The exact time varies by machine and server load, so do not treat another person’s example timing as a baseline.

If the run completes too quickly to measure meaningfully, increase the repetition count gradually. Avoid excessive counts on a shared or production server.

Compare two functions correctly

Run each expression as a separate statement, using the same repetition count:

SELECT BENCHMARK(100000, MD5('test'));
SELECT BENCHMARK(100000, SHA1('test'));

Compare the client-reported elapsed times for the individual statements. Repeat each test several times under similar load; the difference can be small and noisy. These hashing functions have different behavior and security properties, so a faster result does not make one interchangeable with the other. See the MariaDB MD5() and SHA1() guides.

Do not combine the tests into one SELECT and attempt to compare the two result columns:

-- Not a useful comparison: both values are 0, and the client
-- reports only one total execution time for this statement.
SELECT BENCHMARK(100000, MD5('test')),
       BENCHMARK(100000, SHA1('test'));

What BENCHMARK() does not measure

  • Not a query-plan test: It repeats a scalar expression, not an entire table scan, join, or aggregation. For a slow SQL query, inspect its plan using MariaDB EXPLAIN; ANALYZE can execute the query and report actual runtime statistics.
  • Not a precise server CPU timer: The client-reported duration is elapsed statement time and can include overhead. Server load, caching, and execution environment affect comparisons.
  • Not a reason to run huge loops in production: Keep the repetition count controlled and benchmark on a development or isolated test server when possible.

For complete syntax and examples, consult the official MariaDB BENCHMARK() reference. For investigating full-query behavior, see MariaDB’s query-plan guidance.