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

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: