How to optimize MySQL SUM query even after adding index

Question

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table 1:detail_recovery_case = 67532 records and Table 2: y8qel_detailrecovery = 28638 records. I think its taking more time to execute the SUM() Subquery. Can we re-write or optimize this query in any way.

 SELECT 
a.RecoveryCaseID,
a.PatientClientID,
a.ClientID,
a.ClientName,
a.CaseOpenedDate,
a.CaseCloseDate,
a.CaseType,
a.CaseStatus,
a.FundingType,
a.PatientFirstName,
a.PatientLastName,
a.PatientDOB,
(a.GreenedClaimsAmt+a.PharmacyClaimsAmt+a.AdditionalClaimsAmt) as caseamount,
(select sum(c.RecoveryAmt) from detailrecovery AS c 
WHERE a.RecoveryCaseID=c.RecoveryCaseID and a.ClientID=c.ClientID) as recoveryamount 
FROM detail_recovery_case AS a 
JOIN detailrecovery AS b ON (a.RecoveryCaseID=b.RecoveryCaseID and a.ClientID=b.ClientID) 
WHERE a.ClientID=50 GROUP BY a.RecoveryCaseID ORDER BY a.RecoveryCaseID desc;

The following is the explain plan which shows its using all the indexes.

*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: b
 type: index
possible_keys: rcid
 key: rcid
 key_len: 8
 ref: NULL
 rows: 28742
 Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
 id: 1
 select_type: PRIMARY
 table: a
 type: ref
possible_keys: rccid
 key: rccid
 key_len: 8
 ref: frg13b.b.RecoveryCaseID,const
 rows: 1
 Extra: Using index
*************************** 3. row ***************************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: c
 type: ref
possible_keys: rcid
 key: rcid
 key_len: 8
 ref: func,const
 rows: 718
 Extra: NULL
3 rows in set (0.25 sec)

I have allocated 1) 4GB for MySQL Buffer Pool 2) tmp_table_size=297M 3) innodb_sort_buffer_size=50M 4) join_buffer_size=50M

Server version: 5.6.21-log MySQL Community Server (GPL) Windows Server 2008 Ram 8GB Intel Xeon CPU E5-2630L v2 @ 2.40GHz

([email protected]) [frg13b]> show create table y8qel_detail_recovery_case\G
*************************** 1. row ***************************
 Table: y8qel_detail_recovery_case
Create Table: CREATE TABLE `y8qel_detail_recovery_case` (
 `RecoveryCaseID` int(11) NOT NULL,
 `ClientID` int(11) NOT NULL,
 `CaseOpenedDate` date DEFAULT NULL,
 `CaseType` varchar(50) DEFAULT NULL,
 `CaseStatus` varchar(20) DEFAULT NULL,
 `CaseCloseDate` date DEFAULT NULL,
 `Specialist` varchar(100) DEFAULT NULL,
 `Source` varchar(50) DEFAULT NULL,
 `ClientName` varchar(50) DEFAULT NULL,
 `ClientLocation` varchar(50) DEFAULT NULL,
 `FundingType` varchar(50) DEFAULT NULL,
 `EmployerID` int(11) DEFAULT NULL,
 `EmployerPlanNumber` varchar(50) DEFAULT NULL,
 `EmployerName` varchar(50) DEFAULT NULL,
 `RecoveryStatus` varchar(5) DEFAULT NULL,
 `PlanState` varchar(50) DEFAULT NULL,
 `SubscriberID` int(11) DEFAULT NULL,
 `SubscriberFirstName` varchar(50) DEFAULT NULL,
 `SubscriberLastName` varchar(50) DEFAULT NULL,
 `SubscriberSSN` varchar(9) DEFAULT NULL,
 `SubscriberAddressLine1` varchar(50) DEFAULT NULL,
 `SubscriberAddressLine2` varchar(50) DEFAULT NULL,
 `SubscriberCity` varchar(50) DEFAULT NULL,
 `SubscriberState` varchar(50) DEFAULT NULL,
 `SubscriberZip` varchar(50) DEFAULT NULL,
 `PatientID` int(11) DEFAULT NULL,
 `PatientClientID` varchar(50) DEFAULT NULL,
 `PatientFirstName` varchar(50) DEFAULT NULL,
 `PatientLastName` varchar(50) DEFAULT NULL,
 `PatientGender` char(1) DEFAULT NULL,
 `PatientDOB` date DEFAULT NULL,
 `PatientSSN` varchar(9) DEFAULT NULL,
 `PatientPhone` varchar(11) DEFAULT NULL,
 `InjuryDate` date DEFAULT NULL,
 `AttnyFirm1Name` varchar(50) DEFAULT NULL,
 `Attorney1First` varchar(50) DEFAULT NULL,
 `Attoney1Last` varchar(50) DEFAULT NULL,
 `Attorney1Address1` varchar(50) DEFAULT NULL,
 `Attorney1Address2` varchar(50) DEFAULT NULL,
 `Attorney1City` varchar(50) DEFAULT NULL,
 `Attorney1State` varchar(2) DEFAULT NULL,
 `Attorney1Zip` varchar(5) DEFAULT NULL,
 `Attorney1Phone` varchar(10) DEFAULT NULL,
 `AttnyFirm2Name` varchar(50) DEFAULT NULL,
 `Attorney2First` varchar(50) DEFAULT NULL,
 `Attoney2Last` varchar(50) DEFAULT NULL,
 `Attorney2Address1` varchar(50) DEFAULT NULL,
 `Attorney2Address2` varchar(50) DEFAULT NULL,
 `Attorney2City` varchar(50) DEFAULT NULL,
 `Attorney2State` varchar(2) DEFAULT NULL,
 `Attorney2Zip` varchar(5) DEFAULT NULL,
 `Attorney2Phone` varchar(10) DEFAULT NULL,
 `1PCarrierName` varchar(50) DEFAULT NULL,
 `1PClaimNumber` varchar(50) DEFAULT NULL,
 `1PInsuranceAddress1` varchar(50) DEFAULT NULL,
 `1PInsuranceAddress2` varchar(50) DEFAULT NULL,
 `1PInsuranceCity` varchar(50) DEFAULT NULL,
 `1PInsuranceState` varchar(2) DEFAULT NULL,
 `1PInsuranceZip` varchar(5) DEFAULT NULL,
 `1PInsurancePhone` varchar(10) DEFAULT NULL,
 `3PCarrierName` varchar(50) DEFAULT NULL,
 `3PClaimNumber` varchar(50) DEFAULT NULL,
 `3PInsuranceAddress1` varchar(50) DEFAULT NULL,
 `3PInsuranceAddress2` varchar(50) DEFAULT NULL,
 `3PInsuranceCity` varchar(50) DEFAULT NULL,
 `3PInsuranceState` varchar(2) DEFAULT NULL,
 `3PInsuranceZip` varchar(5) DEFAULT NULL,
 `3PInsurancePhone` varchar(10) DEFAULT NULL,
 `StateOfLoss` varchar(50) DEFAULT NULL,
 `GreenedClaimsAmt` decimal(15,2) DEFAULT NULL,
 `PharmacyClaimsAmt` decimal(15,2) DEFAULT NULL,
 `AdditionalClaimsAmt` decimal(15,2) DEFAULT NULL,
 KEY `pln` (`PatientLastName`),
 KEY `pfn` (`PatientFirstName`),
 KEY `pdob` (`PatientDOB`),
 KEY `pid` (`PatientID`),
 KEY `cod` (`CaseOpenedDate`),
 KEY `ccd` (`CaseCloseDate`),
 KEY `gca` (`GreenedClaimsAmt`),
 KEY `pca` (`PharmacyClaimsAmt`),
 KEY `aca` (`AdditionalClaimsAmt`),
 KEY `rccid` (`RecoveryCaseID`,`ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.55 sec)
([email protected]) [frg13b]> show create table y8qel_detailrecovery\G
*************************** 1. row ***************************
 Table: y8qel_detailrecovery
Create Table: CREATE TABLE `y8qel_detailrecovery` (
 `ClientID` int(11) NOT NULL,
 `ClientName` varchar(50) DEFAULT NULL,
 `RecoveryCaseID` int(11) NOT NULL,
 `RecoveryDate` datetime DEFAULT NULL,
 `RecoveredFrom` varchar(50) DEFAULT NULL,
 `RecoveryAmt` decimal(15,2) DEFAULT NULL,
 KEY `rcvd` (`RecoveryDate`),
 KEY `ra` (`RecoveryAmt`),
 KEY `rcid` (`RecoveryCaseID`,`ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

Thanks

• ypercubeᵀᴹ: Indexes on (ClientID, RecoveryCaseID) would be better for this query.
• ypercubeᵀᴹ: And I wonder why you have 2 joins to detailrecovery. Can't you rewrite with only one join (in the FROM clause) and throw away the correlated inline subquery?
• ypercubeᵀᴹ: And why, why, oh why do the tables not have any primary or unique constraint?

Answer 1

Try Summing in a separate query and then joining results:

SELECT 
a.RecoveryCaseID,
a.PatientClientID,
a.ClientID,
a.ClientName,
a.CaseOpenedDate,
a.CaseCloseDate,
a.CaseType,
a.CaseStatus,
a.FundingType,
a.PatientFirstName,
a.PatientLastName,
a.PatientDOB,
a.GreenedClaimsAmt+a.PharmacyClaimsAmt+a.AdditionalClaimsAmt as caseamount,
b.recoveryamount 
FROM detail_recovery_case AS a 
JOIN 
 (select RecoveryCaseID, sum(c.RecoveryAmt) as recoveryamount 
 from detailrecovery
 where ClientID = 50
 group by RecoveryCaseID
 ) AS b
 ON a.RecoveryCaseID=b.RecoveryCaseID 
WHERE a.ClientID=50 
ORDER BY a.RecoveryCaseID desc;

• ypercubeᵀᴹ: Good. It can be improved further by removing the ClientID from the internal group by and removing the external GROUP BY completely.
• Stoleg: @ypercube, Agreed. was in a rush. Updated.