|
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:
-----
| id |
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 |
str |
ref |
filter |
clientid,bigint1,clientlocid |
clientlocid |
bigint1 |
17 |
9 |
const,const,cvt.rl.locationid |
14 (0%) |
Using where; Using rowid filter |
| 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 |
ALL |
PRIMARY |
NULL |
NULL |
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:
-----
| id |
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 `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=903546 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 `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=554911 DEFAULT CHARSET=latin1
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=1021844 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
the config file is as follows:
cat /etc/my.cnf
[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
We already executed the Analyze Table statement and restarted the service after it.
Devang
|