select count(*) from 速度很慢

Question

Select Count(*)
From np_goldidea_vote c, dept_with_path b, np_goldidea_main a
Where c.vote_org_id = b.dept_id
And a.main_id = c.main_id
And a.column_id = '2'
And a.status = 9
Group By b.path 请大家分析下 如何优化这条SQL 目前查询需要12秒 ，其中C表数据量80W条，b表1440条，a表3891条，请高手帮忙下，谢谢，目前这几张表的关联也都建立了。急

Answer 1

单纯把表联接由=的写法改成inner join的写法不会提升效率，因为两者是等价写法。试试下面的写法：Select Count(*)
From np_goldidea_vote c, dept_with_path b, (select main_id,column_id, status from np_goldidea_main where column_id = '2' and status = 9 ) a
Where c.vote_org_id = b.dept_id
And a.main_id = c.main_id
Group By b.path 这个写法的思路是先做a的选择运算，这样在多表连接时，a的数据量会大大降低，从而提高效率。另外，这三个表除了建立默认的主键和唯一性索引外，在b的path上一定要建立索引，这是由于需要按path分组。还有，在连接字段上也要建立索引，这样表连接时，很多时候只需要访问索引而不需要访问表，从而提高效率。

Answer 2

Select Count(*)
From np_goldidea_vote cinner join dept_with_path bon c.vote_org_id = b.dept_id
inner join np_goldidea_main a
on a.main_id = c.main_id
And a.column_id = '2' And a.status = 9
Group By b.path