Optimize Query in MySQL 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 &lt;= '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".

Please provide EXPLAIN SELECT ....

AND wi_training.trn_deleted = 0
AND wi_training.trn_beneficiary_type = 2
AND wi_training.trn_start_date &lt;= '2015-06-30'


Begs for the compound INDEX(trn_beneficiary_type, trn_deleted, trn_start_date).

A compound index is almost always better than Using intersect(...).

(Style recommendation:  Don't prefix all columns with the same thing; it unnecessarily clutters the SQL.)