MySQL Bugs: #68350: query proc - re-execute of stored routine, inefficient query plan

Bug #68350	query proc - re-execute of stored routine, inefficient query plan
Submitted:	12 Feb 2013 14:00	Modified:	29 May 2013 17:30
Reporter:	Aleks Pol	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.11	OS:	Any (7)
Assigned to:		CPU Architecture:	Any
Tags:	query proc

Description:
first time 
call rozklad(2012)       time 0.81 sec

second time 
call rozklad(2012)       time 1 min 51 sec

next time 
call rozklad(2012)       time 1 min 51 sec

How to repeat:
DELIMITER $$

USE `dekanat`$$

DROP PROCEDURE IF EXISTS `rozklad`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `rozklad`(IN iRik YEAR)
BEGIN
SELECT
      trozklad.ID 
    ,  trozpodil.Ses AS piv 
    , tpredmets.GroupID
    , tpredmets.Name AS Predmet
    , tgroups.Name AS Grupa
    ,  CONCAT(tlogin.FName,' ', SUBSTR(tlogin.Name,1,1),'. ', SUBSTR(tlogin.LName,1,1),'.') AS FIO
    , trozklad.T
    ,  CASE trozklad.Type   WHEN 1 THEN 'Лек' WHEN 2 THEN 'Пр' WHEN 3 THEN 'Лаб' END AS TYPE
    , trozklad.Ses
    , trozklad.God
    , trozklad.Den
    , trozklad.Para
    , trozklad.Podgrupa
    , tfakultet.Name  AS Fakultet
    ,  tnkp.Name AS NKP
    , tfnavch.Name AS FNavch
    , tkafedra.Name AS Kafedra
    , tgroups.Kurs
    , IF(trozklad.Type=1,tpredmets.PotokID,NULL) AS PotokID
    , IF(trozklad.Type=2,tpredmets.PotokPrID,NULL) AS PotokPrID
    , IF(trozklad.Type=3,tpredmets.PotokLabID,NULL) AS PotokLabID
    , trozklad.Vichitano
    , a.PotokLec
    , a1.PotokPr
    , a2.PotokLab
 #  ,tpredmets.KafedraID
    , a3.Posada
FROM
  trozklad
    INNER JOIN trozpodil ON (trozklad.RozpodilID = trozpodil.ID)
    INNER JOIN tpredmetsinfo ON (trozpodil.PredmetsInfoID = tpredmetsinfo.ID)
    INNER JOIN tlogin ON (tpredmetsinfo.LoginID = tlogin.ID)
    INNER JOIN tpredmets ON (tpredmetsinfo.PredmetsID = tpredmets.ID)
    INNER JOIN tkafedra ON (tpredmets.KafedraID = tkafedra.ID)
    INNER JOIN tgroups ON (tpredmets.GroupID = tgroups.ID)
    INNER JOIN tfnavch ON (tgroups.FnavchID = tfnavch.ID)
    INNER JOIN tfakultet ON (tgroups.FakultetId = tfakultet.ID) 
    LEFT JOIN tnkp ON (tgroups.NkpID=tnkp.ID)
LEFT JOIN 
(SELECT GROUP_CONCAT( tgroups.Name) AS PotokLec,tpredmets.PotokID  FROM tpredmets 
INNER JOIN tgroups ON (tpredmets.GroupID = tgroups.ID)
WHERE tpredmets.PotokID IS NOT NULL
GROUP BY tpredmets.PotokID  
) AS a ON (tpredmets.PotokID=a.potokid) AND (trozklad.Type=1)
LEFT JOIN 
(SELECT GROUP_CONCAT( tgroups.Name) AS PotokPr,tpredmets.PotokPrID  FROM tpredmets 
INNER JOIN tgroups ON (tpredmets.GroupID = tgroups.ID)
WHERE tpredmets.PotokPrID IS NOT NULL
GROUP BY tpredmets.PotokPrID  
) AS a1 ON (tpredmets.PotokPrID=a1.potokPrid) AND (trozklad.Type=2)   
LEFT JOIN 
(SELECT GROUP_CONCAT( tgroups.Name) AS PotokLab,tpredmets.PotokLabID  FROM tpredmets 
INNER JOIN tgroups ON (tpredmets.GroupID = tgroups.ID)
WHERE tpredmets.PotokLabID IS NOT NULL
GROUP BY tpredmets.PotokLabID  
) AS a2 ON (tpredmets.PotokLabID=a2.potokLabid) AND (trozklad.Type=3)  
LEFT JOIN 
(
SELECT
    CONCAT(IFNULL(tposada.Name,''),' - ',IFNULL(tposada.Stupin,'')) AS Posada
    ,tposadainfo.kafedraid
    ,tposadainfo.loginid
FROM
     tposadainfo 
    LEFT JOIN tposada 
        ON (tposadainfo.PosadaID = tposada.ID)
GROUP BY tposadainfo.kafedraid
    ,tposadainfo.loginid
) AS a3 ON (tpredmets.kafedraid=a3.kafedraid  AND tpredmetsinfo.loginid=a3.loginid  )
WHERE tgroups.Rik=iRik AND trozpodil.noRoz=0
ORDER BY  piv, trozklad.T,  tgroups.Name,trozklad.Den  , trozklad.Para ;           
END$$

DELIMITER ;

Hi Alex,

Please help us to narrow down the cause.  Please send us the schema?

mysqldump --no-defaults -uroot -p --all-databases --no-data --routines > out.sql

Hi Aleks,  I made what I believe to be a simple testcase.  Please, check it.

-------------
drop table if exists `t1`;
create table `t1`(`a` int,key(`a`))engine=innodb;
insert into `t1` values (1),(2),(3),(4),(5),(6),(7);
insert into `t1` select `t1`.`a` from `t1`,`t1` `t2`,
`t1` `t3`,`t1` `t4`,`t1` `t5`;
drop procedure if exists `p1`;

create procedure `p1`() 
select count(*) from `t1` 
left join (select `a` from `t1`) `z` on `z`.`a`=`t1`.`a`;

call `p1`();
call `p1`();
call `p1`();

flush tables;

call `p1`();
call `p1`();
call `p1`();

-------------

I got execution times:

mysql> call `p1`();
+----------+
| count(*) |
+----------+
| 40387228 |
+----------+
1 row in set (2.15 sec)

Query OK, 0 rows affected (2.15 sec)

mysql> call `p1`();
+----------+
| count(*) |
+----------+
| 40387228 |
+----------+
1 row in set (13.00 sec)

Query OK, 0 rows affected (13.00 sec)

mysql> call `p1`();
+----------+
| count(*) |
+----------+
| 40387228 |
+----------+
1 row in set (12.98 sec)

Query OK, 0 rows affected (12.98 sec)
---------------------

Here is a testcase that is more dramatic.

drop table if exists `t1`;
create table `t1`(`a` int,key(`a`))engine=innodb;
set @a:=9;
insert into `t1` values (1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into `t1` select @a:=@a+1 from `t1`,`t1` `t2`,
`t1` `t3`,`t1` `t4`,`t1` `t5`;
drop procedure if exists `p1`;
create procedure `p1`() 
select count(*) from `t1` left join (select `a` from `t1`) `z` on `z`.`a`=`t1`.`a`;
call `p1`();  #1 row in set (0.08 sec)
call `p1`();  #1 row in set (2 min 21.71 sec)

баг остался

Noted in 5.6.12, 5.7.2 changelogs.

Indexed on derived tables that were used during the first invocation
of a stored procedure were not used in subsequent invocations.