Bug #38884 time calculation during a routine
Submitted: 19 Aug 2008 7:40 Modified: 1 Oct 2008 14:34
Reporter: Susanne Ebrecht Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any

[19 Aug 2008 7:40] Susanne Ebrecht
Description:
I worked on bug #38813 and I recognised that when you have a loop of selects in a routine then it won't be displayed the needed time for the single select. Instead the needed time always will be added to the time before.

Example:

mysql> call test_call_multiple_times;
...
1 row in set (0.16 sec)
...
1 row in set (0.31 sec)
...
1 row in set (0.46 sec)
...
...
...
1 row in set (15.09 sec)

Query OK, 0 rows affected (15.09 sec)

I think it would be better to have an output like this:

mysql> call test_call_multiple_times;
...
1 row in set (0.16 sec)
...
1 row in set (0.15 sec)
...
1 row in set (0.15 sec)
...
...
...
1 row in set (0.14 sec)

Query OK, 0 rows affected (15.09 sec)

How to repeat:
Just take the test from bug #38813.

DROP TABLE IF EXISTS my_test_table;
DROP VIEW IF EXISTS my_test_view;

CREATE TABLE my_test_table 
    (uid INT UNSIGNED PRIMARY KEY)
     Engine=MyISAM;

CREATE VIEW my_test_view AS
    SELECT 
        uid
    FROM my_test_table;

DROP PROCEDURE IF EXISTS test_call_multiple_times;

DELIMITER //

CREATE PROCEDURE test_call_multiple_times
()
BEGIN
    DECLARE num_iters INTEGER DEFAULT 10000;
    DECLARE i INTEGER;
    DECLARE value INTEGER;

    SET i = 0;

    my_loop: LOOP
        IF (i = num_iters)
        THEN
            LEAVE my_loop;
        END IF;
            
        SELECT COUNT(*) FROM information_schema.tables INTO value;

        SET i = i + 1;

        IF ((i % 100) = 0)
        THEN
            SELECT i AS 'Iterations completed';
        END IF;
    END LOOP my_loop;
END//

DELIMITER ;

Suggested fix:
This is a feature request.
[1 Oct 2008 14:34] Konstantin Osipov
This is not a feature request.
It's a duplicate of an old bug, which I can't find right now.
[1 Oct 2008 14:34] Konstantin Osipov
Setting back to verified until I can find the original report.