Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.5
-
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" : 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" : 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;