0

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: enter image description here

1 Answer 1

0

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 <= '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.)

7
  • I've added the EXPLAIN SELECT result for the query. Thank you! Mar 27, 2015 at 6:15
  • And have you tried the index I suggest? (I did an edit, too)
    – Rick James
    Mar 27, 2015 at 6:36
  • If many of the rows are "deleted", then performance is being hurt by AND wi_...._deleted = 0
    – Rick James
    Mar 27, 2015 at 6:40
  • Yes! I've tried implementing COMPOUND INDEX as you've suggested. Its taking 3.3min on localhost(windows+chrome). Even simple query with all table JOINs taking a lot of time.. Mar 27, 2015 at 6:47
  • Please provide the EXPLAIN. Something less obvious is going wrong.
    – Rick James
    Mar 27, 2015 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.