[#MDEV-30800] Performance Degraded After Migration From 10.2.25 To 10.5.19

[MDEV-30800] Performance Degraded After Migration From 10.2.25 To 10.5.19 Created: 2023-03-07 Updated: 2023-03-08 Resolved: 2023-03-07
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.5.19
Fix Version/s:	N/A

Type:

Bug

Priority:

Major

Reporter:

Devang

Assignee:

Unassigned

Resolution:

Not a Bug

Votes:

Labels:

None

Environment:

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:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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:

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

Comments

Comment by Alice Sherepa [ 2023-03-07 ]

Could you please add your .cnf file(s) (is it the same as in MDEV-30566?) and run ANALYZE for all involved tables

Comment by Devang [ 2023-03-07 ]

Hi Alice
The Analyze table statement has fixed the issue.
Next, the question do we need to run this statement frequently or from now onward the database system will take care of it?
For your reference, I am attaching my.cnf below too.
=============================================
[client]
port = 3306
socket = /data/mysql.sock

[mysqld]
user = mysql
bind_address = 0.0.0.0
port = 3306
socket = /data/mysql.sock
tmpdir = /backup/tmp
default_time_zone = 'America/Los_Angeles'
skip-name-resolve
performance_schema=1
key_buffer_size=15G
max_allowed_packet=512M
table_cache=8128
table_open_cache=1024
sort_buffer_size=128K
net_buffer_length=8K
read_buffer_size=128K
read_rnd_buffer_size=128K
myisam_sort_buffer_size=4M
group_concat_max_len=2096128
expire_logs_days=15
datadir=/data
innodb_data_file_path=ibdata:2G:autoextend
thread_cache_size=10
innodb_buffer_pool_size=15G
innodb_buffer_pool_instances=15
thread_pool_size=15
join_buffer_size=8M
innodb_log_file_size=1G
innodb_log_files_in_group=3
innodb_log_buffer_size=25M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=100
tmp_table_size=64M
max_heap_table_size=64M
max_connections=200
slow_query_log=1
slow_query_log_file=/var/log/mysqld-slow.log
long_query_time=1
default_storage_engine=myisam
innodb_temp_data_file_path=ibtmp1:512M:autoextend
sql-mode=""
log_warnings=0
#Query Parameter
query_cache_limit=96M
query_cache_min_res_unit=4096
query_cache_size=0
query_cache_type=0
query_cache_wlock_invalidate=0
open_files_limit = 10240
#Below parameters updated on 02-March-2019
innodb_read_io_threads = 8
innodb_write_io_threads = 8
sort_buffer_size = 512K
read_buffer_size = 512K
thread_cache_size=20
open_files_limit = 102400
log-error = /var/log/mysqld.log
binlog_format = STATEMENT
#skip-slave-start=0
log-bin = teak19-bin
log-bin-index = teak19-bin-index
server-id=19
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=test
slave_compressed_protocol=1
relay-log=teak19-relay-bin
default_tmp_storage_engine=MyISAM
optimizer_switch='rowid_filter=off'

#server_audit_events="CONNECT"
#server_audit_file_path="/var/log/server_audit.log"
#server_audit_file_rotate_size=1024000
#server_audit_logging=ON

[mysqld_safe]
open_files_limit = 102400
user = mysql
log-error = /var/log/mysqld.log

=================================
Please suggest for better performance
we have 64GB physical ram with all SSD.
The size of databases per engine is as below

----------------------------------------------------

engine

table_schema

total_size_mb

----------------------------------------------------

NULL	mysql	NULL
Aria	information_schema	0.13
Aria	mysql	6.25
CSV	mysql	0.00
InnoDB	cvt	10741.85
InnoDB	cvta9007	55.02
InnoDB	cvta9008	9.50
InnoDB	cvta9009	12.78
InnoDB	cvta9010	0.34
InnoDB	cvta9011	0.54
InnoDB	cvta9012	0.49
InnoDB	mysql	3.50
MEMORY	information_schema	0.00
MyISAM	cvt	431.66
MyISAM	cvta0000	6098.82
MyISAM	cvta0000_2019_02_15	15263.67
MyISAM	cvta0000_2019_11_01	7183.21
MyISAM	cvta0000_2021_03_05	12327.47
MyISAM	cvta0000_2022_05_06	10622.41
MyISAM	cvta0001	3343.85
MyISAM	cvta0002	57.32
MyISAM	cvta0003	186.92
MyISAM	cvta0004	91.98
MyISAM	cvta0005	2442.66
MyISAM	cvta0006	180.88
MyISAM	cvta0007	3889.60
MyISAM	cvta0008	138.23
MyISAM	cvta0009	36.04
MyISAM	cvta0010	8458.16
MyISAM	cvta0011	600.93
MyISAM	cvta0012	1684.95
MyISAM	cvta0013	353.95
MyISAM	cvta0014	949.95
MyISAM	cvta0015	2570.66
MyISAM	cvta0016	340.75
MyISAM	cvta0017	596.98
MyISAM	cvta0018	404.55
MyISAM	cvta0019	549.97
MyISAM	cvta0020	2114.25
MyISAM	cvta0021	763.58
MyISAM	cvta0027	5266.02
MyISAM	cvta0028	423.96
MyISAM	cvta0029	31.68
MyISAM	cvta0030	492.49
MyISAM	cvta9000	0.00
MyISAM	cvta9007	2844.35
MyISAM	cvta9008	2456.56
MyISAM	cvta9009	10348.57
MyISAM	cvta9010	177.81
MyISAM	cvta9011	428.07
MyISAM	cvta9012	181.06
MyISAM	cvta9013	0.00
MyISAM	cvta9014	0.00
MyISAM	cvta9015	0.00
MyISAM	cvta9016	0.00
MyISAM	cvtp0001	41.95
MyISAM	cvtt0001	7.75
PERFORMANCE_SCHEMA	performance_schema	0.00

----------------------------------------------------

Comment by Alice Sherepa [ 2023-03-07 ]

Great!
You might run ANALYZE periodically after DML operations significantly change the contents of indexed columns or e.g. after creating a new index.

Comment by Devang [ 2023-03-07 ]

Do we need to restart the mariadb process?
We are still observing slowness for a few queries.

Comment by Devang [ 2023-03-08 ]

*Here is one more slow query incident even after analyzing the table executed for every used table.
*

explain SELECT DISTINCT(l.recid) custLocId , CONCAT_WS(' - ', NULLIF(c.name, ''), NULLIF(l.storenbr, ''), NULLIF(l.sdesc, ''), NULLIF(a.address1, '')) custDesc ,
if(str.customerlocid,1,0) custCnt,rl.barcode barcode FROM routelink rl ,location l LEFT JOIN storeretrieve str ON l.recid = str.customerlocid AND str.clientid = '527923'
AND str.clientlocid = '854304' AND str.type = 'B' AND str.bigint1 = '13987' AND (str.endeffdt IS NULL OR str.endeffdt = '0000-00-00' OR str.endeffdt > '2023-03-07'),
company c,address a,locationlink ll ,vendingsubgroup vsg WHERE rl.clientid = '527923' AND rl.locationid = l.recid AND
ll.companyid = c.recid AND l.addressid = a.recid AND l.recid = ll.locationid AND vsg.vendinggroupid = 676 AND l.subgroupid = vsg.recid HAVING custCnt = 0;

Result on 10.5.19:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SIMPLE

ref

clientid,locationid,clientid_2

clientid

const

13840

Using temporary

SIMPLE

eq_ref

PRIMARY,recid,addressid

PRIMARY

cvt.rl.locationid

Using where

SIMPLE

vsg

eq_ref

PRIMARY,vendinggroupid

PRIMARY

cvt.l.subgroupid

Using where

SIMPLE

str

ref

filter

clientid,bigint1,clientlocid

clientlocid

bigint1

const,const,cvt.rl.locationid

14 (0%)

Using where; Using rowid filter

SIMPLE

ref

companyid,locationid

locationid

cvt.rl.locationid

Using where

SIMPLE

eq_ref

PRIMARY

cvt.ll.companyid

SIMPLE

ALL

PRIMARY

NULL

477074

Using where; Using join buffer (flat, BNL join)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Time taken by this query is ...

1623 rows in set (4 min 3.389 sec)

Result on 10.2.25:
-----------------------------------------------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-----------------------------------------------------------------------------------------------------------------------------+

1	SIMPLE	rl	ref	clientid,locationid,clientid_2	clientid	8	const	13840	Using temporary
1	SIMPLE	l	eq_ref	PRIMARY,recid,addressid	PRIMARY	8	cvt.rl.locationid	1	Using where
1	SIMPLE	vsg	eq_ref	PRIMARY,vendinggroupid	PRIMARY	8	cvt.l.subgroupid	1	Using where
1	SIMPLE	ll	ref	companyid,locationid	locationid	9	cvt.rl.locationid	1	Using where
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	8	cvt.ll.companyid	1
1	SIMPLE	a	eq_ref	PRIMARY	PRIMARY	8	cvt.l.addressid	1
1	SIMPLE	str	ref	clientid,bigint1,clientlocid	clientlocid	17	const,const,cvt.rl.locationid	6	Using where

-----------------------------------------------------------------------------------------------------------------------------+

Time taken by this query is ...

1623 rows in set (0.20 sec)

CREATE TABLE `routelink` (
`clientid` bigint(20) NOT NULL DEFAULT 0,
`locationid` bigint(20) NOT NULL DEFAULT 0,
`barcode` varchar(25) DEFAULT NULL,
`type` char(1) DEFAULT NULL,
`status` char(2) DEFAULT NULL,
`specialinstr` varchar(500) DEFAULT NULL,
`endeffdt` date DEFAULT NULL,
`daycodeid` bigint(20) DEFAULT NULL,
`billingdaycodeid` bigint(20) DEFAULT NULL,
`billingperioddaycodeid` bigint(20) DEFAULT NULL,
`memoinv` char(1) DEFAULT NULL,
`billingbasedate` date DEFAULT NULL,
`billingperiodbasedate` date DEFAULT NULL,
`scdaycodeid` bigint(20) DEFAULT NULL,
`basescdate` date DEFAULT NULL,
`scperioddaycodeid` bigint(20) DEFAULT NULL,
`basescperioddate` date DEFAULT NULL,
`billinglocationid` bigint(20) DEFAULT NULL,
`billingpersonid` bigint(20) DEFAULT NULL,
`invoiceform` char(1) DEFAULT 'A',
`memoinvoiceform` char(1) DEFAULT NULL,
`datelastclose` date DEFAULT NULL,
`seasonid` bigint(20) DEFAULT NULL,
`returnsflag` char(1) NOT NULL DEFAULT 'R',
`deliveryslipflag` char(1) NOT NULL DEFAULT 'N',
`pod` char(1) NOT NULL DEFAULT 'N',
`piaflag` char(1) NOT NULL DEFAULT 'O',
`piadaysadv` int(11) DEFAULT NULL,
`dtlastpia` date DEFAULT NULL,
`creditlimit` float NOT NULL DEFAULT 0,
`credithold` char(1) NOT NULL DEFAULT 'N',
`creditterms` int(11) NOT NULL DEFAULT 15,
`invoicemethod` char(1) NOT NULL DEFAULT 'N',
`retnsepcopies` int(11) DEFAULT NULL,
`collinstr` varchar(255) DEFAULT NULL,
`payonscan` char(1) NOT NULL DEFAULT 'N',
`locparamsetid` bigint(20) DEFAULT NULL,
`hawkerstarttime` time DEFAULT NULL,
`hawkerendtime` time DEFAULT NULL,
`hawkercount` int(11) DEFAULT NULL,
`costcodeid` bigint(20) DEFAULT NULL,
`salescodeid` bigint(20) DEFAULT NULL,
`dellabel` char(1) NOT NULL DEFAULT 'N',
`prebundle` char(1) NOT NULL DEFAULT 'N',
`invoicingent` char(1) NOT NULL DEFAULT 'D',
`bonus` float DEFAULT NULL,
KEY `clientid` (`clientid`),
KEY `locationid` (`locationid`),
KEY `barcode` (`barcode`),
KEY `billinglocationid` (`billinglocationid`),
KEY `clientid_2` (`clientid`,`locationid`,`endeffdt`),
KEY `payonscan` (`payonscan`),
KEY `endeffdt` (`endeffdt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `storeretrieve` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`clientid` bigint(20) NOT NULL,
`clientlocid` bigint(20) NOT NULL,
`type` char(1) NOT NULL,
`datet` date NOT NULL,
`datex` date NOT NULL,
`datey` date NOT NULL,
`datez` date NOT NULL,
`customerlocid` bigint(20) NOT NULL,
`integer1` int(11) NOT NULL,
`integer2` int(11) NOT NULL,
`integer3` int(11) NOT NULL,
`integer4` int(11) NOT NULL,
`integer5` int(11) NOT NULL,
`integer6` int(11) NOT NULL,
`bigint1` bigint(20) DEFAULT NULL,
`bigint2` bigint(20) DEFAULT NULL,
`bigint3` bigint(20) DEFAULT NULL,
`float1` float NOT NULL,
`float2` float NOT NULL,
`float3` float NOT NULL,
`float4` float NOT NULL,
`float5` float NOT NULL,
`float6` float NOT NULL,
`strvar1` varchar(255) NOT NULL,
`strvar2` varchar(255) NOT NULL,
`strvar3` varchar(255) NOT NULL,
`strvar4` varchar(255) NOT NULL,
`strvar5` varchar(255) NOT NULL,
`strvar6` varchar(255) NOT NULL,
`reccreatedate` date NOT NULL,
`endeffdt` date NOT NULL,
`deletedate` date NOT NULL,
PRIMARY KEY (`recid`),
KEY `clientid` (`clientid`,`clientlocid`,`type`,`datet`,`customerlocid`),
KEY `bigint1` (`bigint1`,`bigint2`,`bigint3`),
KEY `StrVar1` (`strvar1`),
KEY `clientlocid` (`clientlocid`,`type`,`customerlocid`)
) ENGINE=InnoDB AUTO_INCREMENT=1468139 DEFAULT CHARSET=latin1

CREATE TABLE `locationlink` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`companyid` bigint(20) DEFAULT NULL,
`locationid` bigint(20) DEFAULT NULL,
`locationnbr` varchar(6) DEFAULT NULL,
`pagecode` varchar(5) DEFAULT NULL,
`password` varchar(8) DEFAULT NULL,
`comment` varchar(250) DEFAULT NULL,
`endeffdt` date DEFAULT NULL,
PRIMARY KEY (`recid`),
KEY `companyid` (`companyid`),
KEY `locationid` (`locationid`)
) ENGINE=InnoDB AUTO_INCREMENT=903534 DEFAULT CHARSET=latin1

CREATE TABLE `vendingsubgroup` (
`recid` bigint(20) NOT NULL AUTO_INCREMENT,
`vendinggroupid` bigint(20) NOT NULL DEFAULT 0,
`sdesc` varchar(50) NOT NULL DEFAULT '',
`ldesc` varchar(250) DEFAULT NULL,
`routeid` bigint(20) DEFAULT 0,
PRIMARY KEY (`recid`),
KEY `vendinggroupid` (`vendinggroupid`),
KEY `routeid` (`routeid`),
KEY `sdesc` (`sdesc`)
) ENGINE=InnoDB AUTO_INCREMENT=15638 DEFAULT CHARSET=latin1

Please help

Generated at Thu Feb 08 10:18:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-30800] Performance Degraded After Migration From 10.2.25 To 10.5.19 Created: 2023-03-07 Updated: 2023-03-08 Resolved: 2023-03-07