Optimizing my mysql query

I clearly know that there are plenty of questions already asked on this topic but i couldn't optimize mine. So below is my query:

    select tc.id, tc.name tc, s.name state, d.name district, 
    count(distinct case when curdate() between b.starttime and b.endtime then b.id end) as active,
    (case when count(distinct case when curdate() between b.starttime and b.endtime then b.id end) > 0 then 'active' when tc.status = 'Archived-I' then 'transitioned' when count(distinct case when curdate() between b.starttime and b.endtime then b.id end) = 0 and tc.status != 'Archived-I' then 'Idle' end ) as _status,
    count(distinct(b.id)) as batches, sum(case when sb.status = 'active' then 1 else 0 end) as in_training, count(distinct case when sb.status = 'complete' then sb.student_id end) as trained,
    count(distinct(sa.student_id)) as assessed, count(distinct(sp.student_id)) as placed 
    from training_centers tc left join batches b on b.training_center_id = tc.id 
    left join student_batches sb on b.id = sb.batch_id 
    left join student_assessments sa on sa.batch_id = b.id 
    left join student_placements sp on sp.batch_id = b.id 
    left join states s on s.id = tc.state_id 
    left join districts d on d.id = tc.district_id 
    where tc.implementing_agency_id = 28 
    group by tc.id 
    order by tc.name

Output of EXPLAIN is below:

    id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
    1|SIMPLE|tc|ref|implementing agency|implementing agency|4|const|201|Using temporary; Using filesort
    1|SIMPLE|b|ALL|NULL|NULL|NULL|NULL|11018|
    1|SIMPLE|sb|ref|batch id|batch id|4|ministry_mis.b.id|10|
    1|SIMPLE|sa|ref|batch|batch|4|ministry_mis.b.id|7|
    1|SIMPLE|sp|ALL|NULL|NULL|NULL|NULL|78799|
    1|SIMPLE|s|eq_ref|PRIMARY|PRIMARY|4|ministry_mis.tc.state_id|1|
    1|SIMPLE|d|eq_ref|PRIMARY|PRIMARY|4|ministry_mis.tc.district_id|1|1|

I have already tried query caching and since i am using this query in a php application, i have tried memcache as well. Please help me understand, how can i optimize my query or if something is wrong here.

---

Add an appropriate index on the students_placements table (aliased as sp in the query).

CREATE INDEX student_placements_IX1
    ON student_placements (batch_id, student_id)

The EXPLAIN output is showing a full scan of that table, ("ALL"). With that index, we'd expect the EXPLAIN output to show a ref operation.

Also, on the batches table (aliased as b in the query) add an index like:

CREATE INDEX batches_IX1
    ON batches (training_center_id, id, starttime, endtime)

If all of the columns referenced in the query for a table come from the index, the index is called a "covering index" for the query, and the the EXPLAIN output will show "Using index" in the Extra column.

If id is a UNIQUE or PRIMARY KEY on the training_centers table (aliased as tc in the questy), you might also consider changing the query to do this:

 GROUP BY tc.name, tc.id
 ORDER BY tc.name

A covering index on training_centers , may also be of benefit:

CREATE INDEX training_centers_IX1
    ON training_centers (implementing_agency_id, name, id, state_id, district_id, status)

We'd need to look at the EXPLAIN after the indexes are added, and go from there. (Sometimes, MySQL can make use of an index to avoid a "Using filesort" operation, if an appropriate index is available.)

链接地址: http://www.djcxy.com/p/62456.html

上一篇: 使用子查询查询MYSQL中的查询优化？

下一篇: 优化我的mysql查询