Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.19
-
None
-
10.5.19 on CentOs-7
Description
Dear Supporter,
We have faced one more issue after migrating from 10.2.25 to 10.5.19.
The following query started taking more than 2 minutes on 10.5.19 while it was taking just 1 second on 10.2.25.
=====================================
SELECT ps.recid psRecId, CONCAT_WS('-', NULLIF(DATE_FORMAT(ps.recdt, '%m/%d/%y'), ''), NULLIF(TIME_FORMAT(ps.recdt, '%H:%i:%s'), '')) recDt,
c.name compName,ps.distproductid, CONCAT_WS(' - ', NULLIF(l.storenbr, ''), NULLIF(l.sdesc, ''), NULLIF(a.address1, '')) locDesc, p.ldesc prodDesc,
DATE_FORMAT(ps.saledate, '%m/%d%/%y') saleDate, ps.specificproductid spId, SUM(ps.quantity) soldQty, ps.unitprice unitPrice , ps.procflag FROM company c,
product p, payonscan ps LEFT JOIN companymap cm ON cm.twcerecid = ps.customerlocid AND cm.clientid = 474476 AND fieldcode = 'LO' ,
location l LEFT JOIN address a ON a.recid = l.addressid WHERE ps.clientlocid = 757549 AND ps.customerlocid = l.recid AND ps.companyid = c.recid AND
ps.distproductid = p.recid AND ps.saledate <= '2023-03-06' AND ps.saledate >= '2023-02-01' AND ( ( ps.companyid = 0 OR ps.companyid IS NULL) OR
( ps.companylocid = 0 OR ps.companylocid IS NULL) OR ( ps.pubproductid = 0 OR ps.pubproductid IS NULL) OR ( ps.distproductid = 0 OR ps.distproductid IS NULL) OR
( ps.specificproductid = 0 OR ps.specificproductid IS NULL) OR (cm.recid = 0 OR cm.recid IS NULL)) AND ps.procflag = 'X' GROUP BY ps.recid HAVING soldQty !=0
ORDER BY ps.saledate;
=====================================
Explain the result on 10.5.25:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 | SIMPLE | ps | range | specificproductid,distproductid,procflag,customerid,customerlocid,clientlocid,companyid,clientlocid_2 | clientlocid_2 | 14 | NULL | 42186 | Using index condition; Using where; Using temporary; Using filesort |
1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | cvt.ps.distproductid | 1 | |
1 | SIMPLE | cm | ref | clientid,fieldcode,twcerecid | twcerecid | 8 | cvt.ps.customerlocid | 1 | Using where |
1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | cvt.ps.companyid | 1 | |
1 | SIMPLE | l | eq_ref | PRIMARY,recid | PRIMARY | 8 | cvt.ps.customerlocid | 1 | |
1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 8 | cvt.l.addressid | 1 | Using where |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Explain the result on 10.5.19:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 | SIMPLE | ps | range | specificproductid,distproductid,procflag,customerid,customerlocid,clientlocid,companyid,clientlocid_2 | clientlocid_2 | 14 | NULL | 42186 | Using index condition; Using where; Using temporary; Using filesort |
1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | cvt.ps.distproductid | 1 | |
1 | SIMPLE | cm | ref | clientid,fieldcode,twcerecid | twcerecid | 8 | cvt.ps.customerlocid | 1 | Using where |
1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | cvt.ps.companyid | 1 | |
1 | SIMPLE | l | ref | PRIMARY,recid | recid | 8 | cvt.ps.customerlocid | 1 | |
1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 477507 | Using where |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The design of tables used in this query:
table company:
CREATE TABLE `company` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`locationid` bigint(20) DEFAULT NULL,
`personid` bigint(20) DEFAULT NULL,
`type` char(1) DEFAULT NULL,
`logonmsg` varchar(250) DEFAULT NULL,
`matchcheck` int(11) DEFAULT NULL,
`taxexempt` char(1) NOT NULL DEFAULT 'N',
`logo` mediumblob DEFAULT NULL,
`nbremployees` int(11) DEFAULT NULL,
`tickersymbol` varchar(15) DEFAULT NULL,
`website` varchar(50) DEFAULT NULL,
`endeffdt` date DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `name` (`name`),
KEY `personid` (`personid`)
) ENGINE=InnoDB AUTO_INCREMENT=554906 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
table product:
CREATE TABLE `product` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`clientid` bigint(20) NOT NULL DEFAULT 0,
`producttype` char(2) NOT NULL DEFAULT 'PU',
`producttypeid` bigint(20) NOT NULL,
`seasonid` bigint(20) DEFAULT NULL,
`vendorlocid` bigint(20) NOT NULL DEFAULT 0,
`sdesc` varchar(50) NOT NULL DEFAULT '',
`adesc` varchar(20) DEFAULT NULL,
`ldesc` varchar(250) DEFAULT NULL,
`seodesc` varchar(255) DEFAULT NULL,
`seotitle` varchar(255) NOT NULL,
`daycodeid` bigint(20) NOT NULL DEFAULT 0,
`holidaysetid` bigint(20) DEFAULT NULL,
`payablesdaycodeid` bigint(20) NOT NULL DEFAULT 0,
`billingperioddaycodeid` bigint(20) DEFAULT NULL,
`billingperiod` char(1) NOT NULL DEFAULT 'W',
`basepayablesdate` date DEFAULT NULL,
`basepayablesperioddate` date NOT NULL DEFAULT '0000-00-00',
`feedaycodeid` bigint(20) DEFAULT NULL,
`basefeedate` date DEFAULT NULL,
`feeperioddaycodeid` bigint(20) DEFAULT NULL,
`basefeeperioddate` date DEFAULT NULL,
`scdaycodeid` bigint(20) DEFAULT NULL,
`basescdate` date DEFAULT NULL,
`scperioddaycodeid` bigint(20) DEFAULT NULL,
`basescperioddate` date DEFAULT NULL,
`basepubdate` date DEFAULT NULL,
`DateLastClose` date DEFAULT NULL,
`returnsbasis` char(1) DEFAULT NULL,
`returnsfactor` int(11) DEFAULT NULL,
`returnsexception` char(1) DEFAULT NULL,
`pickupcode` char(2) NOT NULL DEFAULT 'ND',
`drawrounding` int(11) NOT NULL DEFAULT 1,
`pubgroupmethod` char(1) NOT NULL DEFAULT 'N',
`effdt` date NOT NULL,
`endeffdt` date DEFAULT NULL,
`sortseqnbr` int(11) DEFAULT NULL,
`sortseqnbr2` int(11) DEFAULT NULL,
`returnspct` float DEFAULT NULL,
`daystring` varchar(250) DEFAULT NULL,
`drawchangelead` int(11) NOT NULL DEFAULT 0,
`costcodereq` char(1) NOT NULL DEFAULT 'Y',
`dispublocid` bigint(20) DEFAULT NULL,
`adjustflag` char(1) NOT NULL DEFAULT 'N',
`adjustlimitpct` float DEFAULT NULL,
`pricebasisfactor` float NOT NULL DEFAULT 1,
`pricebasisunitid` bigint(20) DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `clientid` (`clientid`,`vendorlocid`),
KEY `sdesc` (`sdesc`),
KEY `sortseqnbr` (`sortseqnbr`),
KEY `daycodeid` (`daycodeid`),
KEY `seasonid` (`seasonid`),
KEY `endeffdt` (`endeffdt`),
KEY `producttype` (`producttype`),
KEY `holidaysetid` (`holidaysetid`),
KEY `payablesdaycodeid` (`payablesdaycodeid`),
KEY `billingperioddaycodeid` (`billingperioddaycodeid`),
KEY `billingperiod` (`billingperiod`),
KEY `feedaycodeid` (`feedaycodeid`),
KEY `feeperioddaycodeid` (`feeperioddaycodeid`),
KEY `returnsbasis` (`returnsbasis`),
KEY `returnsexception` (`returnsexception`),
KEY `pickupcode` (`pickupcode`),
KEY `pubgroupmethod` (`pubgroupmethod`),
KEY `drawrounding` (`drawrounding`),
KEY `returnspct` (`returnspct`),
KEY `producttypeid` (`producttypeid`),
KEY `effdt` (`effdt`)
) ENGINE=InnoDB AUTO_INCREMENT=16127 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
table payonscan:
CREATE TABLE `payonscan` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`recdt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`chain` varchar(20) DEFAULT NULL,
`distributor` varchar(20) DEFAULT NULL,
`storenumber` varchar(20) DEFAULT NULL,
`processorid` bigint(20) DEFAULT NULL,
`companyid` bigint(20) DEFAULT NULL,
`customerid` bigint(20) DEFAULT NULL,
`clientlocid` bigint(20) DEFAULT NULL,
`companylocid` bigint(20) DEFAULT NULL,
`customerlocid` bigint(20) DEFAULT NULL,
`upc` varchar(20) DEFAULT NULL,
`pubproductid` bigint(20) DEFAULT NULL,
`distproductid` bigint(20) DEFAULT NULL,
`saledate` date DEFAULT NULL,
`specificproductid` bigint(20) DEFAULT NULL,
`quantity` float DEFAULT NULL,
`unitprice` float DEFAULT NULL,
`postdate` date NOT NULL,
`remitdate` date DEFAULT NULL,
`reference` varchar(50) NOT NULL,
`procflag` char(1) NOT NULL DEFAULT 'N',
`acctgprocflag` char(1) NOT NULL DEFAULT 'N',
`periodenddt` date DEFAULT NULL,
`activesession` bigint(20) NOT NULL DEFAULT 0,
`mapcode` char(1) NOT NULL DEFAULT 'P',
`lastnotifydate` date DEFAULT NULL,
`reportdate` date DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `storenumber` (`storenumber`),
KEY `specificproductid` (`specificproductid`),
KEY `chain` (`chain`),
KEY `distributor` (`distributor`),
KEY `distproductid` (`distproductid`),
KEY `procflag` (`procflag`),
KEY `cp29` (`processorid`,`chain`,`distributor`,`storenumber`,`upc`,`saledate`),
KEY `customerid` (`clientlocid`,`customerid`,`companyid`),
KEY `acctgprocflag` (`acctgprocflag`,`periodenddt`),
KEY `customerlocid` (`customerlocid`),
KEY `reportdate` (`reportdate`),
KEY `clientlocid` (`clientlocid`),
KEY `periodenddt` (`periodenddt`),
KEY `remitdate` (`remitdate`),
KEY `companyid` (`companyid`),
KEY `clientlocid_2` (`clientlocid`,`saledate`,`procflag`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21882047 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
table companymap:
CREATE TABLE `companymap` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`clientid` bigint(20) DEFAULT 0,
`clientlocid` bigint(20) DEFAULT NULL,
`companyid` bigint(20) DEFAULT NULL,
`companylocid` bigint(20) DEFAULT NULL,
`companycode` char(3) DEFAULT NULL,
`fieldcode` char(2) NOT NULL DEFAULT '',
`companyvalue` varchar(30) NOT NULL,
`twcerecid` bigint(20) NOT NULL DEFAULT 0,
`pubsubgroupid` bigint(20) DEFAULT NULL,
`chaingroupid` bigint(20) DEFAULT NULL,
`charvar` char(1) DEFAULT NULL,
`comment` varchar(250) DEFAULT NULL,
`effdt` date DEFAULT NULL,
`endeffdt` date DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `companycode` (`companycode`,`fieldcode`,`companyvalue`,`twcerecid`),
KEY `companyid` (`companyid`),
KEY `clientid` (`clientid`),
KEY `pubsubgroupid` (`pubsubgroupid`),
KEY `fieldcode` (`fieldcode`),
KEY `companyvalue` (`companyvalue`),
KEY `twcerecid` (`twcerecid`),
KEY `chaingroupid` (`chaingroupid`),
KEY `clientlocid` (`clientlocid`),
KEY `effdt` (`effdt`),
KEY `endeffdt` (`endeffdt`)
) ENGINE=InnoDB AUTO_INCREMENT=912689 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
table location:
CREATE TABLE `location` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`barcode` varchar(15) DEFAULT NULL,
`sdesc` varchar(35) DEFAULT NULL,
`ldesc` varchar(250) DEFAULT NULL,
`type` char(1) DEFAULT NULL,
`latlong` varchar(30) DEFAULT NULL,
`addressid` bigint(20) DEFAULT NULL,
`logonmsg` varchar(250) DEFAULT NULL,
`endeffdate` date DEFAULT NULL,
`effdate` date DEFAULT NULL,
`storenbr` varchar(15) DEFAULT NULL,
`subgroupid` bigint(20) DEFAULT NULL,
`buildingid` bigint(20) DEFAULT NULL,
`salestaxsetid` bigint(20) DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `storenbr` (`storenbr`),
KEY `effdate` (`effdate`),
KEY `buildingid` (`buildingid`),
KEY `recid` (`recid`,`endeffdate`,`effdate`),
KEY `addressid` (`addressid`)
) ENGINE=InnoDB AUTO_INCREMENT=903525 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
table address:
CREATE TABLE `address` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`description` varchar(40) DEFAULT NULL,
`address1` varchar(40) DEFAULT NULL,
`address2` varchar(40) DEFAULT NULL,
`address3` varchar(40) DEFAULT NULL,
`address4` varchar(40) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state` char(2) DEFAULT NULL,
`postalcode` varchar(10) DEFAULT NULL,
`phone` varchar(25) DEFAULT NULL,
`fax` varchar(15) DEFAULT NULL,
`neighborhoodid` bigint(20) DEFAULT NULL,
`county` varchar(40) DEFAULT NULL,
`latlong` varchar(30) DEFAULT NULL,
`latitude` float(20,6) DEFAULT 0.000000,
`longitude` float(20,6) DEFAULT 0.000000,
`buildingid` bigint(20) DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `postalcode` (`postalcode`),
KEY `phone` (`phone`),
KEY `latitude` (`latitude`),
KEY `longitude` (`longitude`),
KEY `buildingid` (`buildingid`)
) ENGINE=InnoDB AUTO_INCREMENT=1021830 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
This is happening only when these tables are belonging to INNODB.
Devang