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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
A simple query would be:
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):
Here is a pretty horrible query. It is a Cartesian product, using non-ansi style join.
use master select * from sys.objects, sys.indexes