Complex MySQL Query occasionally runs nonterminating

Question

Every 30 minutes I am running a rather complex SQL query from a PHP script via cron updating a statistical table.

Usually the update works and takes about 2-3 minutes processing about 40.000 records. Ocassionally this query runs infinitely long, up to hours with no result. Viewing MySQL processes in PHPMyAdmin then shows the query with a time of 50.000 seconds and above.

This leads to the problem the table isn't being updated anymore all day. Why is that happening?

The query is complex, but non-special:

SELECT `a`.`id`
       AS
       `id`,
       `a`.`debitor`
       AS `debitor`,
       `a`.`wnummer`
       AS `wnummer`,
       `a`.`konto`
       AS `konto`,
       `a`.`datum`
       AS `datum`,
       `a`.`name`
       AS `name`,
       `a`.`name2`
       AS `name2`,
       `a`.`land`
       AS `land`,
       `a`.`plz`
       AS `plz`,
       `a`.`ort`
       AS `ort`,
       `a`.`str`
       AS `str`,
       `a`.`beschichterdatum`
       AS `beschichterdatum`,
       `a`.`werkstattdatum`
       AS `werkstattdatum`,
       `a`.`plandatum`
       AS `plandatum`,
       `a`.`kommision`
       AS `kommision`,
       `a`.`status`
       AS `status`,
       (SELECT `protokoll`.`ts`
        FROM   `protokoll`
        WHERE  ( `protokoll`.`auftrag_id` = `a`.`id` )
               AND ( `protokoll`.`status_neu` = `a`.`status` )
        ORDER  BY `protokoll`.`ts` DESC
        LIMIT  1)
       AS `status_ts`,
       `a`.`tourname`
       AS `tourname`,
       `a`.`anlage`
       AS `anlage`,
       `a`.`user`
       AS `user`,
       `a`.`vertreter`
       AS `vertreter`,
       `a`.`druckmodus`
       AS `druckmodus`,
       (SELECT GROUP_CONCAT(DISTINCT `position`.`maschine` ORDER BY
               `position`.`maschine` ASC SEPARATOR ', ') AS
               `maschine`
        FROM   `position`
        WHERE  ( `position`.`auftrag_id` = `a`.`id` )
               AND ( `position`.`status` = 20 )
               AND ( Length(`position`.`maschine`) > 0 ))
       AS
       `maschine`,
       (SELECT GROUP_CONCAT(DISTINCT `position`.`beschichter` ORDER
               BY
               `position`.`beschichter` ASC SEPARATOR ', ') AS
               `beschichter`
        FROM   `position`
        WHERE  ( `position`.`auftrag_id` = `a`.`id` )
               AND ( `position`.`status` = 50 )
               AND ( Length(`position`.`beschichter`) > 0 ))
       AS
       `beschichter`,
       (SELECT DISTINCT `position`.`rueckstandinfo` AS
                        `rueckstandinfo`
        FROM   `position`
        WHERE  ( `position`.`auftrag_id` = `a`.`id` )
        ORDER  BY IF(( Length(`position`.`rueckstandinfo`) > 0 ), 1,
                  0) DESC
                  ,
                  COUNT(*) DESC
        LIMIT  1)
       AS `rueckstandinfo`,
       (SELECT COUNT(*)
        FROM   `position`
        WHERE  ( `position`.`auftrag_id` = `a`.`id` ))
       AS `menge`,
       (SELECT COUNT(*)
        FROM   `position`
        WHERE  ( `position`.`auftrag_id` = `a`.`id` )
               AND ( `position`.`schrott` = 1 ))
       AS `schrott`,
       (SELECT SUM(`position`.`menge` -
                   `position`.`schrott`)
        FROM   `position`
        WHERE  ( ( `position`.`auftrag_id` = `a`.`id` )
                 AND ( ( CASE
                           WHEN ( `position`.`status` < 41 ) THEN (
                           To_days(`a`.`werkstattdatum`) - To_days(NOW()) )
                           WHEN ( `position`.`status` < 66 ) THEN (
                           To_days(`a`.`beschichterdatum`) - To_days(NOW()) )
                           WHEN ( `position`.`status` < 100 ) THEN (
                           To_days(`a`.`plandatum`) - To_days(NOW()) )
                         END ) < 0 ) ))
       AS `rueckstaendig`,
       ( CASE
           WHEN ( `a`.`status` < 41 ) THEN ( To_days(`a`.`werkstattdatum`) -
                                             To_days(NOW()) )
           WHEN ( `a`.`status` < 66 ) THEN (
           To_days(`a`.`beschichterdatum`) - To_days(
           NOW()) )
           WHEN ( `a`.`status` < 100 ) THEN (
           To_days(`a`.`plandatum`) - To_days(NOW())
                                            )
         END )
       AS `kalendertage`
FROM   `auftrag` `a`

The tables have MyISAM engine.

EXPLAIN output:

Schema auftrag:

CREATE TABLE `auftrag`
  (
     `id`                  INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Auftrag',
     `debitor`             VARCHAR(255) DEFAULT NULL COMMENT 'Kunde',
     `wnummer`             VARCHAR(255) DEFAULT NULL COMMENT 'W-Nr.',
     `konto`               VARCHAR(255) DEFAULT NULL COMMENT 'Konto',
     `vertreter`           INT(11) NOT NULL DEFAULT '0' COMMENT 'Vertreter',
     `preisliste`          INT(11) NOT NULL DEFAULT '0' COMMENT 'Preisliste',
     `datum`               DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
     COMMENT
     'Auftragseingang',
     `name`                VARCHAR(255) DEFAULT NULL COMMENT 'Name',
     `name2`               VARCHAR(255) DEFAULT NULL COMMENT 'Name 2',
     `land`                VARCHAR(255) DEFAULT NULL COMMENT 'Land',
     `plz`                 VARCHAR(255) DEFAULT NULL COMMENT 'PLZ',
     `ort`                 VARCHAR(255) DEFAULT NULL COMMENT 'Ort',
     `str`                 VARCHAR(255) DEFAULT NULL COMMENT 'Strasse',
     `str2`                VARCHAR(255) DEFAULT NULL COMMENT 'Strasse 2',
     `beschichterdatum`    DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
     COMMENT
     'Beschichtung',
     `werkstattdatum`      DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
     COMMENT
     'Werkstatt',
     `plandatum`           DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'Versand',
     `kiste`               INT(1) NOT NULL DEFAULT '0' COMMENT
     'Anzahl Schleifkisten',
     `anlage`              DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
     COMMENT
     'Auftragsanlage',
     `user`                INT(11) NOT NULL DEFAULT '0' COMMENT 'Benutzer',
     `menge_sofortschrott` INT(11) NOT NULL DEFAULT '0',
     `menge_gesamt`        INT(11) NOT NULL DEFAULT '0' COMMENT
     'Menge Lieferschein',
     `menge_realvomkunden` INT(11) NOT NULL DEFAULT '0' COMMENT 'Menge erhalten'
     ,
     `menge_beschrift`     INT(11) NOT NULL DEFAULT '0' COMMENT
     'Menge Beschriftung',
     `bemerkung`           TEXT COMMENT 'Bemerkung',
     `rueckstandinfo`      VARCHAR(255) DEFAULT NULL COMMENT 'Info Rueckstand',
     `bem_c206`            TEXT,
     `bem_c207`            TEXT,
     `bem_c208`            TEXT,
     `bem_c209`            TEXT,
     `kommision`           VARCHAR(255) DEFAULT NULL COMMENT 'Kommission',
     `status`              INT(11) NOT NULL DEFAULT '0' COMMENT 'Status Auftrag'
     ,
     `belegart`            VARCHAR(255) NOT NULL DEFAULT 'Auftrag' COMMENT
     'Belegart',
     `emailapkd`           VARCHAR(255) DEFAULT NULL COMMENT
     'eMail Arbeitsplan Kunde',
     `emailapvt`           VARCHAR(255) DEFAULT NULL COMMENT
     'eMail Arbeitsplan Vertreter',
     `rech_name1`          VARCHAR(255) DEFAULT NULL COMMENT 'Rechnung Name',
     `rech_name2`          VARCHAR(255) DEFAULT NULL COMMENT 'Rechnung Name 2',
     `rech_land`           VARCHAR(255) DEFAULT NULL COMMENT 'Rechnung Land',
     `rech_plz`            VARCHAR(255) DEFAULT NULL COMMENT 'Rechnung PLZ',
     `rech_ort`            VARCHAR(255) DEFAULT NULL COMMENT 'Rechnung Ort',
     `rech_str`            VARCHAR(255) DEFAULT NULL COMMENT 'Rechnung Strasse',
     `rech_str2`           VARCHAR(255) DEFAULT NULL COMMENT
     'Rechnung Strasse 2',
     `gesendet_an`         TEXT COMMENT 'Arbeitsplan gesendet',
     `angebotmail`         DATETIME DEFAULT NULL COMMENT 'Angebot Intern',
     `requestangebot`      SMALLINT(1) NOT NULL DEFAULT '0' COMMENT
     'Angebot',
     `gutschein`           CHAR(1) NOT NULL DEFAULT 'N' COMMENT 'Gutschein',
     `tourname`            VARCHAR(255) DEFAULT NULL COMMENT 'Tourenname Pickup'
     ,
     `druckmodus`          VARCHAR(255) NOT NULL DEFAULT '5/12/""/DL/""'
     COMMENT
     'Schl,Beschicht,Teillief,DL-ZS',
     `fremdkonto`          VARCHAR(255) DEFAULT NULL COMMENT 'Fremdkonto',
     `versandart`          VARCHAR(255) NOT NULL DEFAULT '9' COMMENT
     'Versandart',
     `c201`                VARCHAR(255) DEFAULT NULL,
     `ansprechpartner`     VARCHAR(255) DEFAULT NULL COMMENT 'AP',
     PRIMARY KEY (`id`),
     KEY `status` (`status`),
     KEY `tourname` (`tourname`),
     KEY `wnummer` (`wnummer`),
     KEY `debitor` (`debitor`),
     KEY `user` (`user`)
  )
ENGINE=myisam
AUTO_INCREMENT=91809
DEFAULT CHARSET=utf8 

Schema position:

CREATE TABLE `position`
  (
     `id`             INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Position',
     `auftrag_id`     INT(11) NOT NULL DEFAULT '0' COMMENT 'Auftrag',
     `artikel_id`     VARCHAR(30) NOT NULL COMMENT 'Artikel',
     `menge`          DECIMAL(6, 0) NOT NULL DEFAULT '0' COMMENT 'Menge',
     `listpreis`      DECIMAL(10, 2) NOT NULL DEFAULT '0.00' COMMENT
     'Listenpreis',
     `rabatt`         DECIMAL(6, 2) NOT NULL DEFAULT '0.00' COMMENT
     'Rabatt Prozent',
     `preis`          DECIMAL(13, 2) NOT NULL DEFAULT '0.00' COMMENT 'Preis',
     `wl_rab`         DECIMAL(6, 2) DEFAULT NULL COMMENT 'Rabatt',
     `wl_preis`       DECIMAL(13, 2) DEFAULT NULL COMMENT 'Preis',
     `schrott`        DECIMAL(6, 0) NOT NULL DEFAULT '0' COMMENT 'Schrott',
     `maschine`       VARCHAR(5) DEFAULT NULL COMMENT 'Maschine',
     `beschichter`    VARCHAR(5) DEFAULT NULL COMMENT 'Beschichter',
     `beschichtung`   VARCHAR(30) DEFAULT NULL COMMENT 'Beschichtung',
     `sk_kond`        VARCHAR(2) DEFAULT NULL COMMENT 'Schneidkanten NB/VB',
     `status`         SMALLINT(6) NOT NULL DEFAULT '15' COMMENT 'Status',
     `infotext`       TEXT COMMENT 'Infotext',
     `artikeltext`    TEXT COMMENT 'Artikeltext',
     `schleiftext`    TEXT COMMENT 'Schleifanweisung',
     `name_en`        VARCHAR(255) DEFAULT NULL COMMENT 'Artikeltext EN',
     `name_it`        VARCHAR(255) DEFAULT NULL COMMENT 'Artikeltext IT',
     `name_fr`        VARCHAR(255) DEFAULT NULL COMMENT 'Artikeltext FR',
     `c214`           VARCHAR(200) DEFAULT NULL,
     `enddatum`       DATETIME DEFAULT NULL,
     `reklamation`    CHAR(1) NOT NULL DEFAULT 'N' COMMENT 'Reklamation',
     `werkzeugtyp`    ENUM('Standard', 'LohntNichtInfo', 'Sonderwerkzeug',
     'Kundenwerkzeug') NOT NULL DEFAULT 'Standard' COMMENT 'Werkzeugtyp',
     `durchmesser`    VARCHAR(50) DEFAULT NULL COMMENT 'Durchmesser',
     `rueckstandinfo` VARCHAR(255) DEFAULT NULL COMMENT 'Rueckstandinfo',
     PRIMARY KEY (`id`),
     KEY `status` (`status`),
     KEY `auftrag_id` (`auftrag_id`),
     KEY `artikel_id` (`artikel_id`),
     KEY `maschine` (`maschine`),
     KEY `beschichter` (`beschichter`),
     KEY `rueckstandinfo` (`rueckstandinfo`),
     KEY `enddatum` (`enddatum`)
  )
ENGINE=myisam
AUTO_INCREMENT=2518917
DEFAULT CHARSET=utf8 

Schema protokoll:

CREATE TABLE `protokoll`
  (
     `id`         INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `usr_id`     INT(11) NOT NULL COMMENT 'Benutzer',
     `auftrag_id` INT(11) NOT NULL COMMENT 'Auftrag',
     `pos_id`     INT(11) NOT NULL COMMENT 'Position',
     `artikel_id` VARCHAR(255) NOT NULL COMMENT 'Artikel',
     `status_alt` SMALLINT(6) DEFAULT NULL COMMENT 'Status ALT',
     `status_neu` SMALLINT(6) DEFAULT NULL COMMENT 'status NEU',
     `info`       VARCHAR(255) DEFAULT NULL COMMENT 'Infotext',
     `ts`         DATETIME NOT NULL COMMENT 'Zeitstempel',
     PRIMARY KEY (`id`),
     KEY `ts` (`ts`),
     KEY `auftrag_id_2` (`auftrag_id`, `artikel_id`, `status_neu`)
  )
ENGINE=myisam
AUTO_INCREMENT=361183
DEFAULT CHARSET=utf8 

Answer 1

I recomend you FORCE all indexes that showed in explain. Sometimes mysql can think that no index is better, as result you get longer time.

Answer 2

If your query works "most" of the time then some outside force is affecting it. What "other" cron jobs do you have running? Are other database queries being run when your query is running? Is your insert/query wrapped in a transaction?

So my suggestions:

Use a transaction in your PHP script ... PHP + MySQL transactions examples

Try one of the non-default locking modes. MySQL's default is REPEATABLE READ. If dirty reads are fine try READ UNCOMMITTED, or if that's not acceptable try the more ACID'y READ COMMITTED. http://www.itecsoftware.com/with-nolock-table-hint-equivalent-for-mysql

Answer 3

Here's what I would try to do... as per extracting the subqueries, I am able to get 3 elements wrapped into a single query / join result. Couldn't apply to deal with your limit 1 instances, so left them as in-line selects.

SELECT 
      a.id,
      a.debitor,
      a.wnummer,
      a.konto,
      a.datum,
      a.`name`,
      a.name2,
      a.land,
      a.plz,
      a.ort,
      a.`str`,
      a.beschichterdatum,
      a.werkstattdatum,
      a.plandatum,
      a.kommision,
      a.`status`,
      ( SELECT protokoll.ts
           FROM   protokoll
           WHERE  ( protokoll.auftrag_id = a.id )
              AND ( protokoll.status_neu = a.`status` )
           ORDER  BY protokoll.ts DESC
           LIMIT  1) AS status_ts,
      a.tourname,
      a.anlage,
      a.`user`,
      a.vertreter,
      a.druckmodus,
      JoinMaschine.maschine,
      JoinBeschichter.beschichter,
      MengeSchrottRueck.menge,
      MengeSchrottRueck.schrott,
      MengeSchrottRueck.rueckstaendig,

      ( SELECT DISTINCT `position`.rueckstandinfo AS rueckstandinfo
           FROM   `position`
           WHERE  ( `position`.auftrag_id = a.id )
           ORDER BY 
              IF(( Length(`position`.rueckstandinfo) > 0 ), 1, 0) DESC,
              COUNT(*) DESC
           LIMIT  1 ) AS rueckstandinfo,
       ( CASE WHEN ( a.`status` < 41 ) 
                 THEN ( To_days(a.werkstattdatum) - To_days(NOW()) )
              WHEN ( a.`status` < 66 ) 
                 THEN ( To_days(a.beschichterdatum) - To_days( NOW()) )
              WHEN ( a.`status` < 100 ) 
                 THEN (To_days(a.plandatum) - To_days(NOW()) )
         END ) AS kalendertage
FROM   
   auftrag a
      JOIN ( SELECT p.auftrag_id,
                    GROUP_CONCAT(DISTINCT p.maschine 
                                 ORDER BY p.maschine ASC SEPARATOR ', ') AS maschine
               from `position` p
               WHERE p.`status` = 20
                 AND Length(p.maschine) > 0 
               group by p.auftrag_id ) as JoinMaschine
         ON a.ID = JoinMaschine.auftrag_id

      JOIN ( SELECT p.auftrag_id,
                    GROUP_CONCAT(DISTINCT p.beschichter 
                                 ORDER BY p.beschichter ASC SEPARATOR ', ') AS beschichter
                FROM `position` p
               WHERE  ( `position`.auftrag_id = a.id )
                 AND ( `position`.`status` = 50 )
                 AND ( Length(`position`.beschichter) > 0 )
               GROUP BY p.auftrag_id ) AS JoinBeschichter
         ON a.id = JoinBeschichter.auftrag_id

      JOIN ( SELECT p.auftrag_id,
                    COUNT(*) as Menge,
                    SUM( IF( p.schrott = 1, 1, 0 )) as schrott,
                    SUM(p.menge - p.schrott * 
                          IF( ( p.`status` < 41 AND To_days(a.werkstattdatum) - To_days(NOW()) < 0)
                           OR ( p.`status` < 66 AND To_days(a.beschichterdatum) - To_days(NOW()) < 0 )
                           OR ( p.`status` < 100 AND To_days(a.plandatum) - To_days(NOW()) < 0 ), 1, 0 )
                       ) AS rueckstaendig
                FROM `position` p
                group by p.auftrag_id ) as MengeSchrottRueck
         ON a.id = MengeSchrottRueck.auftrag_id

Answer 4

You can try EXPLAIN your_query to see more details about query.

But I would definitely replace subqueries in select section with JOIN and GROUP BY wherever possible - subquery in select must be calculated for each row. Menge and Schrott columns seems easy to replace to me, for example.