Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

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;
share|improve this answer
    
As a sidenote the equivalent for SQL Server is WAITFOR DELAY '00:00:02' –  Ben Brocka Nov 11 '11 at 17:18
    
select sleep(2) is the answer that I was looking for; I can't set long_query_time myself. –  David Nov 11 '11 at 18:21
    
Short and sweet is always neat. Nice Stored Procedure as well. +1 !!! –  RolandoMySQLDBA Nov 12 '11 at 16:50
add comment

Here is a pretty horrible query. It is a Cartesian product, using non-ansi style join.

use master
select * from sys.objects, sys.indexes
share|improve this answer
    
neat...But I don't think that's mysql. –  Derek Downey Nov 11 '11 at 17:08
    
oops. Sorry. I didn't notice it was for MySQL. –  datagod Nov 14 '11 at 0:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.