Details

    Description

      http://bugs.mysql.com/bug.php?id=68350
      first time
      call rozklad(2012) time 0.946 sec

      second time
      call rozklad(2012) time 46.55 sec

      next time
      call rozklad(2012) time 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

      1. ,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 ;

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

      Attachments

        1. explain.csv
          2 kB
        2. mdev4171.sql.gz
          5.97 MB
        3. out 10.0.1.sql
          582 kB
        4. query massages.txt
          1 kB
        5. SHOW VARIABLES.csv
          12 kB
        6. test.test.gz
          5.97 MB

        Issue Links

          Activity

            covering_keys is part of TABLE so it is no surprize that it does not stay after reopen.

            sanja Oleksandr Byelkin added a comment - covering_keys is part of TABLE so it is no surprize that it does not stay after reopen.

            I couldn't reproduce it on latest 5.5 and 10.0-base.

            pomyk Patryk Pomykalski added a comment - I couldn't reproduce it on latest 5.5 and 10.0-base.
            elenst Elena Stepanova added a comment - - edited

            Still reproducible on the current 5.5 and 10.0 (revno 4471).

            The upstream bug was fixed in 5.6.12, so there is no point waiting any longer:

            revno: 5057
            revision-id: neeraj.x.bisht@oracle.com-20130430092409-20y1i3lrtlx1izmn
            parent: luis.soares@oracle.com-20130429215526-ry0xgj1pvkaj0dxf
            committer: Neeraj Bisht <neeraj.x.bisht@oracle.com>
            branch nick: 5.6
            timestamp: Tue 2013-04-30 14:54:09 +0530
            message:
              Bug#16346367 : QUERY PROC RE-EXECUTE OF STORED ROUTINE, INEFFICIENT QUERY PLAN
              
              Problem:-
              In derived tables implementation, We only get to use indexes on derived tables 
              on the first execution of a procedure and  in later execution we are not able 
              to use indexes.
              
              
              Analysis:-
              In case of derived tables, we call add_key_field() to add new fake keys for 
              range optimizer. To choose the best key to read data. After creating the actual 
              keys from this fake keys. 
              We set the variable in TABLE_LIST::derived_keys_ready, to show that actual 
              keys are created.
              This table_list is maintained in the memory after our exection is completed.                       
              So when we call our procedure second time, this TABLE_LIST::derived_keys_ready 
              is already set and we assume that actual keys are already being created, but we 
              will not find any key, which cause change in query execution plan.
              
              Solution:-
              Reset the TABLE_LIST::derived_keys_ready at the time JOIN_TAB::cleanup.

            Please consider either merging the fix if applicable or fixing it independently.

            elenst Elena Stepanova added a comment - - edited Still reproducible on the current 5.5 and 10.0 (revno 4471). The upstream bug was fixed in 5.6.12, so there is no point waiting any longer: revno: 5057 revision-id: neeraj.x.bisht@oracle.com-20130430092409-20y1i3lrtlx1izmn parent: luis.soares@oracle.com-20130429215526-ry0xgj1pvkaj0dxf committer: Neeraj Bisht <neeraj.x.bisht@oracle.com> branch nick: 5.6 timestamp: Tue 2013-04-30 14:54:09 +0530 message: Bug#16346367 : QUERY PROC RE-EXECUTE OF STORED ROUTINE, INEFFICIENT QUERY PLAN Problem:- In derived tables implementation, We only get to use indexes on derived tables on the first execution of a procedure and in later execution we are not able to use indexes. Analysis:- In case of derived tables, we call add_key_field() to add new fake keys for range optimizer. To choose the best key to read data. After creating the actual keys from this fake keys. We set the variable in TABLE_LIST::derived_keys_ready, to show that actual keys are created. This table_list is maintained in the memory after our exection is completed. So when we call our procedure second time, this TABLE_LIST::derived_keys_ready is already set and we assume that actual keys are already being created, but we will not find any key, which cause change in query execution plan. Solution:- Reset the TABLE_LIST::derived_keys_ready at the time JOIN_TAB::cleanup. Please consider either merging the fix if applicable or fixing it independently.

            Yes. It looks like a fixed. But inability to make tests automatically prevent me to find what commit did it.

            sanja Oleksandr Byelkin added a comment - Yes. It looks like a fixed. But inability to make tests automatically prevent me to find what commit did it.

            People

              sanja Oleksandr Byelkin
              polyatykin Polyatykin Aleksey
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.