MySQL Forums
Forum List  »  Optimizer & Parser

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2501
January 06, 2017 05:48AM
826
January 06, 2017 12:28PM
Re: Query not always using indexes
906
January 09, 2017 05:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.