优化我的mysql查询

我清楚地知道，在这个话题上已经提出了很多问题，但我不能优化我的。 所以下面是我的查询：

    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

EXPLAIN的输出如下：

    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|

我已经试过查询缓存，因为我在php应用程序中使用这个查询，我也尝试了memcache。 请帮助我理解，如何优化我的查询或者如果这里出现问题。

---

在students_placements表中添加一个适当的索引（在查询中将其替换为sp ）。

CREATE INDEX student_placements_IX1
    ON student_placements (batch_id, student_id)

EXPLAIN输出显示该表格的完整扫描（“ALL”）。 使用该索引，我们希望EXPLAIN输出显示ref操作。

另外，在batches表（在查询中用b来表示）添加一个索引，如：

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

如果查询中引用的所有列都来自索引，则该索引称为查询的“覆盖索引”，而EXPLAIN输出将在Extra列中显示“使用索引”。

如果id是training_centers表上的唯一键或主键（在questy中别名为tc ），那么也可以考虑更改查询来执行此操作：

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

training_centers的覆盖指数也可能有好处：

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

索引添加后，我们需要查看EXPLAIN，然后从那里开始。 （有时，如果有适当的索引可用，MySQL可以使用索引来避免“使用文件”操作。）

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

上一篇: Optimizing my mysql query

下一篇: perm database query