Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23513

slower query execution plan selected compared to older versions

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.5
    • 10.5
    • Optimizer
    • None
    • Windows 10

    Description

      MariaDB 10.4.0 and MySQL 5.7.19 uses eq_ref access , where MariaDB 10.5.5 uses ref access with a different(slower) query plan and a different key.
      Query execution time is around 47 msec (10.4.0) vs. 204 msec (10.5.5) on my test system.

      Query:
      explain
      select distinct hidref, workerid, 2 as db
      from
      (select distinct idworker from shifts WHERE ( ('2020-08-12' between shifts.startdate and shifts.enddate) AND ((shifts.lineID) = 11) ) ) as shifts
      inner join
      ( select workerid, hidref from attendreg
      where ( (attendreg.expired is Null) OR (attendreg.expired >= '2020-08-12') ) AND ((attendreg.attdate)<='2020-08-12') AND ((attendreg.idattendreg)>=294928)
      ) as attendreg
      on attendreg.workerid = shifts.idworker
      inner join
      docchange on docchange.hid = attendreg.hidref
      WHERE ( ((docchange.hstat)=1) AND ((docchange.hid)>=5838) ) ;

      Explain Output: (eq vs. eq_ref difference with docchange table)
      10.5.5
      [

      { "id" : 1, "select_type" : "PRIMARY", "table" : "docchange", "type" : "ref", "possible_keys" : "PRIMARY,statdid,hstat_idx", "key" : "statdid", "key_len" : "4", "ref" : "const", "rows" : "1255", "Extra" : "Using where; Using index; Using temporary" }

      ,

      { "id" : 1, "select_type" : "PRIMARY", "table" : "attendreg", "type" : "ref", "possible_keys" : "PRIMARY,hidworker_idx,workerkey_idx,cover_idx", "key" : "hidworker_idx", "key_len" : "4", "ref" : "mesterlista.docchange.HID", "rows" : "44", "Extra" : "Using index condition; Using where" }

      ,

      { "id" : 1, "select_type" : "PRIMARY", "table" : "<derived2>", "type" : "ref", "possible_keys" : "key0", "key" : "key0", "key_len" : "4", "ref" : "mesterlista.attendreg.workerid", "rows" : "1", "Extra" : "Distinct" }

      ,

      { "id" : 2, "select_type" : "DERIVED", "table" : "shifts", "type" : "ref", "possible_keys" : "lineidkey_idx,groupline", "key" : "lineidkey_idx", "key_len" : "4", "ref" : "const", "rows" : "241", "Extra" : "Using where; Using temporary" }

      ]

      10.4.0
      [

      { "id" : 1, "select_type" : "PRIMARY", "table" : "<derived2>", "type" : "ALL", "possible_keys" : NULL, "key" : NULL, "key_len" : NULL, "ref" : NULL, "rows" : 241, "Extra" : "Using temporary" }

      ,

      { "id" : 1, "select_type" : "PRIMARY", "table" : "attendreg", "type" : "ref", "possible_keys" : "PRIMARY,hidworker_idx,workerkey_idx,cover_idx", "key" : "workerkey_idx", "key_len" : "4", "ref" : "shifts.idworker", "rows" : 121, "Extra" : "Using index condition; Using where" }

      ,

      { "id" : 1, "select_type" : "PRIMARY", "table" : "docchange", "type" : "eq_ref", "possible_keys" : "PRIMARY,statdid,hstat_idx", "key" : "PRIMARY", "key_len" : "4", "ref" : "mesterlista.attendreg.hidref", "rows" : 1, "Extra" : "Using where; Distinct" }

      ,

      { "id" : 2, "select_type" : "DERIVED", "table" : "shifts", "type" : "ref", "possible_keys" : "lineidkey_idx,groupline", "key" : "lineidkey_idx", "key_len" : "4", "ref" : "const", "rows" : 241, "Extra" : "Using where; Using temporary" }

      ]

      And the tables involved:
      CREATE TABLE `attendreg` (
      `idattendreg` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `workerid` int(10) unsigned NOT NULL,
      `hidref` int(10) unsigned NOT NULL,
      `attdate` date NOT NULL,
      `expired` date DEFAULT NULL,
      `editor` text COLLATE utf8_unicode_ci DEFAULT NULL,
      `mystamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      PRIMARY KEY (`idattendreg`),
      KEY `hidworker_idx` (`hidref`,`workerid`),
      KEY `workerkey_idx` (`workerid`),
      KEY `cover_idx` (`workerid`,`hidref`,`attdate`),
      CONSTRAINT `hidkey` FOREIGN KEY (`hidref`) REFERENCES `docchange` (`HID`),
      CONSTRAINT `workerkey` FOREIGN KEY (`workerid`) REFERENCES `t_workers` (`boschid`) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB AUTO_INCREMENT=450517 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

      CREATE TABLE `docchange` (
      `HID` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `DID` int(10) unsigned NOT NULL,
      `maker` int(10) unsigned NOT NULL,
      `dep` int(10) unsigned NOT NULL,
      `hdate_begin` date NOT NULL,
      `hdate_end` date NOT NULL,
      `chdate` date NOT NULL,
      `doknr` varchar(255) CHARACTER SET utf8 NOT NULL,
      `dokcm` varchar(255) CHARACTER SET utf8 NOT NULL,
      `hrem` text COLLATE utf8_unicode_ci DEFAULT NULL,
      `link` text CHARACTER SET utf8 DEFAULT NULL,
      `hstat` int(10) unsigned NOT NULL,
      `doktype` int(10) unsigned NOT NULL,
      `hdate_milestone` date NOT NULL,
      PRIMARY KEY (`HID`),
      KEY `hdate_end` (`hdate_end`),
      KEY `dokcm` (`dokcm`),
      KEY `dokid` (`doknr`),
      KEY `hdate_begin` (`hdate_begin`),
      KEY `hdate_milestone` (`hdate_milestone`),
      KEY `chdate` (`chdate`),
      KEY `T_doctypeDocChange` (`doktype`),
      KEY `T_depDocChange` (`dep`),
      KEY `T_emailsDocChange` (`maker`),
      KEY `statdid` (`hstat`,`DID`),
      KEY `DID_idx` (`DID`),
      KEY `hstat_idx` (`hstat`),
      CONSTRAINT `docchange-dochistory` FOREIGN KEY (`DID`) REFERENCES `dochistory` (`ID`),
      CONSTRAINT `docchange-t_dep` FOREIGN KEY (`dep`) REFERENCES `t_dep` (`dep_id`),
      CONSTRAINT `docchange-t_doctype` FOREIGN KEY (`doktype`) REFERENCES `t_doctype` (`type_id`),
      CONSTRAINT `docchange-t_emails` FOREIGN KEY (`maker`) REFERENCES `t_emails` (`userid`),
      CONSTRAINT `docchange-t_hstat` FOREIGN KEY (`hstat`) REFERENCES `t_hstat` (`hstat_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=11519 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

      CREATE TABLE `shifts` (
      `idshifts` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `lineid` int(10) unsigned NOT NULL,
      `team` tinyint(1) NOT NULL,
      `idworker` int(10) unsigned NOT NULL,
      `startdate` date NOT NULL,
      `enddate` date NOT NULL,
      `groupid` int(10) unsigned NOT NULL,
      `xplvl` tinyint(1) unsigned NOT NULL DEFAULT 1,
      `editor` text COLLATE utf8_unicode_ci DEFAULT NULL,
      `mystamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      PRIMARY KEY (`idshifts`),
      KEY `workeridkey_idx` (`idworker`),
      KEY `lineidkey_idx` (`lineid`),
      KEY `tgroupkey_idx` (`groupid`),
      KEY `wsteamkey` (`team`),
      KEY `xpidx` (`xplvl`),
      KEY `idstart` (`idworker`,`startdate`,`enddate`),
      KEY `groupline` (`lineid`,`groupid`,`team`),
      KEY `idline` (`idworker`,`lineid`) COMMENT 'TRS2,3,5,7 külső join használja',
      CONSTRAINT `lineidkey` FOREIGN KEY (`lineid`) REFERENCES `linehistory` (`lineID`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `tgroupkey` FOREIGN KEY (`groupid`) REFERENCES `t_target` (`target_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `workeridkey` FOREIGN KEY (`idworker`) REFERENCES `t_workers` (`boschid`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `wsteamkey` FOREIGN KEY (`team`) REFERENCES `schedules` (`wsid`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `xpkey` FOREIGN KEY (`xplvl`) REFERENCES `t_xplvl` (`lvlid`) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB AUTO_INCREMENT=8110 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

      Attachments

        1. 10.4-explain.png
          10.4-explain.png
          29 kB
        2. 10.5-explain.png
          10.5-explain.png
          30 kB
        3. explain-10.5_force_index.png
          explain-10.5_force_index.png
          40 kB
        4. trace-MariaDB-10.4.6.json
          39 kB
        5. trace-MariaDB-10.5.5.json
          46 kB
        6. trace-MySQL-5.7.19.json
          50 kB

        Activity

          People

            psergei Sergei Petrunia
            Eperbab Zoltán Szalai (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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