如何在MySQL中使用子查询优化多个连接的查询

Question

I've defined following tables in my application to fetch the report for every district according to the training date.

wi_individual_g(ind_id, ind_district_id, ...)
wi_individual_p(ind_id,prg_id, ind_dalit (yes/no), ind_madhesi (yes/no), ...)
wi_training(trn_id, trn_start_date, trn_ben_type, ...)
wi_indv_training(trn_id, ind_id)
wi_district(dst_id,dst_name)

My problem: The report has to be generated to count individuals district-wise who are associated with the training between the given trn_start_date. The application has predefined date range with the quarters as defined below:

$quarter=array('y1q3'=>array('2013-02-01','2013-03-31'),'y1q4'=>array('2013-04-01','2013-06-30')
,'y2q1'=>array('2013-07-01','2013-09-30'),'y2q2'=>array('2013-10-01','2013-012-31'),'y2q3'=>array('2014-01-01','2014-03-31'),'y2q4'=>array('2014-04-01','2014-06-30')
,'y3q1'=>array('2014-07-01','2014-09-30'),'y3q2'=>array('2014-10-01','2014-012-31'),'y3q3'=>array('2015-01-01','2015-03-31'),'y3q4'=>array('2015-04-01','2015-06-30')
,'y4q1'=>array('2015-07-01','2015-09-30'),'y4q2'=>array('2015-10-01','2015-012-31'),'y4q3'=>array('2016-01-01','2016-03-31'),'y4q4'=>array('2016-04-01','2016-06-30')
,'y5q1'=>array('2016-07-01','2016-09-30'),'y5q2'=>array('2016-10-01','2016-012-31'),'y5q3'=>array('2017-01-01','2017-03-31'),'y5q4'=>array('2017-04-01','2017-06-30')
,'y6q1'=>array('2017-07-01','2017-09-30'),'y6q2'=>array('2017-10-01','2017-012-31'),'y6q3'=>array('2018-01-01','2018-03-31'),'y6q4'=>array('2018-04-01','2018-06-30')); 

If the trn_start_date is choosen as Y4Q4 then, the query must count individuals district-wise for each date range as: Y1(Q1-Q4), Y2(Q2-Q4), Y3(Q1-Q4), Y4(Q1-Q4) separately with single query as:

Y1  Y2    Y3    Y4  Y5  Y6
8   3948  3511  0   0   0

AS a solution, I applied following query:

SELECT wi_district.dst_name, 
COUNT(DISTINCT(CASE WHEN wi_training.trn_start_date BETWEEN '2017-07-01' AND '2018-06-30' AND 
ind_dalit='yes' THEN wi_individual_g.ind_id END)) AS y6 , 
COUNT(DISTINCT(CASE WHEN wi_training.trn_start_date BETWEEN '2016-07-01' AND '2017-06-30' AND     ind_dalit='yes' THEN wi_individual_g.ind_id END)) AS y5 , 
COUNT(DISTINCT(CASE WHEN wi_training.trn_start_date BETWEEN '2015-07-01' AND '2016-06-30' AND ind_dalit='yes' THEN wi_individual_g.ind_id END)) AS y4 , 
COUNT(DISTINCT(CASE WHEN wi_training.trn_start_date BETWEEN '2014-07-01' AND '2015-06-30' AND ind_dalit='yes' 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' AND ind_dalit='yes' 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' AND ind_dalit='yes' 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 <='2018-06-30' 
GROUP BY wi_district.dst_name

But this query takes more than 5 minutes to execute and that's worst. I also applied the index on fields but achieved somewhat same result. I would be thankful if someone provide me the best solution.

Answer 1

I've found the way to increase the performance by 3-times:

At first : the query took around 128 secs
After suggestion: the query took around 78 secs
Further modification: the query took around 23 secs
---------------------------------------------------------------------------------
SELECT d.dst_name,
COUNT(DISTINCT(CASE WHEN a.trn_start_date BETWEEN '2014-07-01' AND '2015-06-30' THEN a.ind_id END)) AS y3 , 
COUNT(DISTINCT(CASE WHEN a.trn_start_date BETWEEN '2013-07-01' AND '2014-06-30' THEN a.ind_id END)) AS y2 , 
COUNT(DISTINCT(CASE WHEN a.trn_start_date BETWEEN '2013-02-01' AND '2013-06-30' THEN a.ind_id END)) AS y1  
FROM 
(
    SELECT g.ind_district_id,g.ind_id,t.trn_start_date,t.trn_beneficiary_type
    FROM wi_individual_g g
    INNER JOIN wi_indv_training wit ON g.ind_id = wit.ind_id AND wit.is_deleted = 0 AND g.ind_deleted=0 AND g.ind_is_recepient='yes'
    INNER JOIN wi_training t ON wit.trn_id = t.trn_id AND t.trn_beneficiary_type=2 AND t.trn_deleted = 0
) a
INNER JOIN wi_individual_p p ON p.ind_id=a.ind_id
INNER JOIN wi_district d ON d.dst_id=a.ind_district_id
WHERE p.ind_dalit='yes'
GROUP BY d.dst_name;

As a whole, the performance has been increased by 6-times from my previous query. Thank you for the suggestion @DRapp

If anyone having best solution to increase the performance, I would like to thank him!