I’ve following tables with respective fields
1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data 2) wi_individual_p(ind_id,prg_id,...) Around 250K data 3) wi_training(trn_id,trn_start_date,trn_deleted,trn_beneficiary_type,...) - Around 25K data 4) wi_indv_training(ind_id,trn_id,is_deleted) - Around 450K data 5) wi_district(dst_id,dst_name) - Around 75 data
I was required to define a query to report unique individuals who are participated in the training within the given ranges of training dates (dst_name wise). So, I created following QUERY to fetch the records
SELECT wi_district.dst_name, COUNT(DISTINCT (CASE WHEN wi_training.trn_start_date BETWEEN '2014-07-01' AND '2015-06-30' THEN wi_individual_g.ind_id END)) AS y3, COUNT(DISTINCT (CASE WHEN wi_training.trn_start_date BETWEEN '2013-07-01' AND '2014-06-30' THEN wi_individual_g.ind_id END)) AS y2, COUNT(DISTINCT (CASE WHEN wi_training.trn_start_date BETWEEN '2013-02-01' AND '2013-06-30' THEN wi_individual_g.ind_id END)) AS y1 FROM wi_individual_g INNER JOIN wi_individual_p ON wi_individual_p.ind_id = wi_individual_g.ind_id AND wi_individual_g.ind_is_recepient = 'yes' INNER JOIN wi_district ON wi_district.dst_id = wi_individual_g.ind_district_id AND wi_individual_g.ind_deleted = 0 INNER JOIN wi_indv_training ON wi_indv_training.ind_id = wi_individual_g.ind_id AND wi_indv_training.is_deleted = 0 INNER JOIN wi_training ON wi_training.trn_id = wi_indv_training.trn_id AND wi_training.trn_deleted = 0 AND wi_training.trn_beneficiary_type = 2 AND wi_training.trn_start_date <= '2015-06-30' GROUP BY wi_district.dst_name
The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE. This query is taking around 4 mins to execute and fetch the records. How can I apply other optimizations to the present query? Please provide me some solutions in MySQL!
The EXPLAIN for the above QUERY is:
The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE.
Please learn about “compound indexes”.
EXPLAIN SELECT ....
AND wi_training.trn_deleted = 0 AND wi_training.trn_beneficiary_type = 2 AND wi_training.trn_start_date <= '2015-06-30'
Begs for the compound
INDEX(trn_beneficiary_type, trn_deleted, trn_start_date).
A compound index is almost always better than
(Style recommendation: Don’t prefix all columns with the same thing; it unnecessarily clutters the SQL.)