patternsqlMajor

Write a slow query to test slow query logging?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

loggingqueryslowwritetest

Problem

Is there a simple query that would take > 2 sec so that I can test the slow query logger?

I am looking for something like a generic recursive or iterative statement.

Solution

A simple query would be:

SELECT SLEEP(2);

You want to iterate it?

DELIMITER $
DROP FUNCTION IF EXISTS `iterateSleep` $
CREATE FUNCTION `iterateSleep` (iterations INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE remainder INT;

    SET remainder = iterations;

    read_loop: LOOP     
        IF remainder=0 THEN
            LEAVE read_loop;
        END IF;

        SELECT SLEEP(2) INTO @test;
        SET remainder = remainder - 1;          
    END LOOP;

    RETURN iterations;
END $
DELIMITER ;

-- TO TEST IT OUT
mysql> SELECT iterateSleep(2);
+-----------------+
| iterateSleep(2) |
+-----------------+
|               2 |
+-----------------+
1 row in set (4.01 sec)

Alternatively if you just want to test your slow_query_log, change 'long_query_time' to 0 (to log all queries):

SET long_query_time=0;

Code Snippets

SELECT SLEEP(2);

DELIMITER $$
DROP FUNCTION IF EXISTS `iterateSleep` $$
CREATE FUNCTION `iterateSleep` (iterations INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE remainder INT;

    SET remainder = iterations;

    read_loop: LOOP     
        IF remainder=0 THEN
            LEAVE read_loop;
        END IF;

        SELECT SLEEP(2) INTO @test;
        SET remainder = remainder - 1;          
    END LOOP;

    RETURN iterations;
END $$
DELIMITER ;

-- TO TEST IT OUT
mysql> SELECT iterateSleep(2);
+-----------------+
| iterateSleep(2) |
+-----------------+
|               2 |
+-----------------+
1 row in set (4.01 sec)

SET long_query_time=0;

Context

StackExchange Database Administrators Q#7777, answer score: 29

Revisions (0)

No revisions yet.