Re: Slow query in mysql 5.7.20

[Murawweh Daher]

Hi,

i use innodb storage engine, and buffer pool size 14G and total memory 16G.
First table.
CREATE TABLE `sdp_drs` (
`accounteventid` varchar(100) DEFAULT NULL,
`cdrid` varchar(100) DEFAULT NULL,
`msisdn` int(15) DEFAULT NULL,
`orgintransactiondt` varchar(50) DEFAULT NULL,
`transactionid` varchar(50) DEFAULT NULL,
`nodeid` varchar(50) DEFAULT NULL,
`nodetype` varchar(50) DEFAULT NULL,
`sdpid` varchar(50) DEFAULT NULL,
`adjrecordtype` int(2) DEFAULT NULL,
`adjaction` int(2) DEFAULT NULL,
`serviceclass` varchar(5) DEFAULT NULL,
`balancebefore` varchar(20) DEFAULT NULL,
`balanceafter` varchar(20) DEFAULT NULL,
`adjustmentamount` varchar(20) DEFAULT NULL,
`daflag` int(1) DEFAULT NULL,
`uaflag` int(1) DEFAULT NULL,
`offerflag` int(1) DEFAULT NULL,
`sdpadjrecordtype` varchar(50) DEFAULT NULL,
`isduplicated` int(1) DEFAULT NULL,
`originnodetype` varchar(50) DEFAULT NULL,
`originnodeid` varchar(50) DEFAULT NULL,
`adjustmenttimestamp` timestamp NULL DEFAULT NULL,
`filename` varchar(100) DEFAULT NULL,
KEY `msisdn` (`msisdn`),
KEY `accounteventid` (`accounteventid`),
KEY `adjustmenttimestamp` (`adjustmenttimestamp`),
KEY `adjustmentamount` (`adjustmentamount`),
KEY `isduplicated` (`isduplicated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



second table.
CREATE TABLE `air_drs` (
`accounteventid` varchar(100) COLLATE utf8_bin NOT NULL,
`airrecordtype` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`localseqno` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`msisdn` int(15) DEFAULT NULL,
`transactiondt` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`transactionid` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`hostname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`voucherbasedr` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`refiltype` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`refillprofileid` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`segmentid` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`transactionamount` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`balancebefore` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`balanceafter` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`acountgroupid` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`welcomestatus` varchar(1) COLLATE utf8_bin DEFAULT NULL,
`extdata1` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`extdata2` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`extdata3` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`extdata4` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`currentserviceclassid` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`permanentserviceclassid` varchar(5) COLLATE utf8_bin DEFAULT NULL,
`daflag` int(1) DEFAULT NULL,
`uaflag` int(1) DEFAULT NULL,
`offerflag` int(1) DEFAULT NULL,
`originnodetype` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`originhostname` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`airtimestamp` timestamp NULL DEFAULT NULL,
`filename` varchar(100) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`accounteventid`),
UNIQUE KEY `accounteventid_UNIQUE` (`accounteventid`),
KEY `msisdn` (`msisdn`),
KEY `accounteventid` (`accounteventid`),
KEY `airtimestamp` (`airtimestamp`),
KEY `voucherbasedr` (`voucherbasedr`),
KEY `airrecordtype` (`airrecordtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



Thanks All.