Re: Query not always using indexes
Posted by:
K DBA
Date: January 09, 2017 05:18AM
Thanks, both plans are for the same query. It runs every day and mainly picks the good plan, but sometimes the bad.
The create table statements and query:
CREATE TABLE `contributionschedules` (
`contractnumber` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`id` int(11) NOT NULL,
`contributor` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
`frequency` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
`frequency_occurrence` int(11) DEFAULT NULL,
`amount` decimal(21,6) DEFAULT NULL,
`next_collection_date` datetime DEFAULT NULL,
`pot_number` int(11) NOT NULL,
`salary` decimal(21,6) DEFAULT NULL,
`salary_percentage` decimal(21,6) DEFAULT NULL,
`monthlyamount` decimal(21,6) DEFAULT NULL,
`annualamount` decimal(21,6) DEFAULT NULL,
`frequency_number` int(11) DEFAULT NULL,
`effective_date` date DEFAULT NULL,
`actual_annual_amount` decimal(21,6) DEFAULT NULL,
`derived_monthly_amount` decimal(21,6) DEFAULT NULL,
`derived_salary` decimal(21,6) DEFAULT NULL,
`derived_contributions_count` int(11) DEFAULT NULL,
PRIMARY KEY (`contractnumber`,`contributor`,`pot_number`),
UNIQUE KEY `contributionschedules_pk` (`contractnumber`,`contributor`,`pot_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `contributions` (
`contractnumber` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`contributor` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`contribution_date` date NOT NULL,
`contribution_type` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`contribution_status` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`net_value` decimal(21,6) DEFAULT NULL,
`gross_value` decimal(21,6) DEFAULT NULL,
KEY `contributions_a_contractnumber` (`contractnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
update contributionschedules schedules
join (
select cs.contractnumber, cs.contributor, sum(gross_value) as grossvalue,
count(*) numberpayments, (sum(gross_value) / count(*)) as derived_amount,
((sum(gross_value) / count(*)) * (sch.frequency_number / sch.frequency_occurrence)) / 12 as monthly_amount
from contributions cs
join contributionschedules sch on (sch.contractnumber = cs.contractnumber
and sch.contributor = cs.contributor)
where cs.contractnumber like 'TM%'
and cs.contribution_status = 'P'
and cs.contribution_type = 'M'
and cs.contribution_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
group by cs.contractnumber, cs.contributor, sch.frequency_number, sch.frequency_occurrence
) as calculatedvalues on calculatedvalues.contractnumber = schedules.contractnumber
and calculatedvalues.contributor = schedules.contributor
set actual_annual_amount = grossvalue,
derived_monthly_amount = round(monthly_amount,2),
derived_contributions_count = numberpayments
The tables get recreated and loaded each day, and this query is run immediately after the load.