当前位置：首页 → 问答吧 → 帮忙看下sql语句，执行了15秒多

帮忙看下sql语句，执行了15秒多

时间：2011-06-30

来源：互联网

下面这条语句执行了15秒多，大家帮忙看下能不能优化下，还是我数据库建的不合理。非常感谢！！！
SQL code


SELECT DISTINCT b.project subproject, b.id subid, d.staffname dpm, a.tname tname, c.staffname tleader, g.location tlocation, l.project project, m.staffname tpl, a.id tid
FROM port_team a
LEFT JOIN p_project b ON a.subproject = b.id
LEFT JOIN p_staff c ON a.tleader = c.id
LEFT JOIN p_staff d ON b.pmanagera = d.id
LEFT JOIN p_team_member e ON a.id = e.teama AND e.ctype = 'staff'
LEFT JOIN p_location g ON a.tlocation = g.id
LEFT JOIN p_team_member h ON h.sid = a.tleader
LEFT JOIN p_team k ON h.teama = k.id
LEFT JOIN p_project i ON h.subprojecta = i.id
LEFT JOIN p_project j ON h.projecta = j.id
LEFT JOIN p_team ea ON ea.id = e.teama
LEFT JOIN p_project eb ON eb.id = e.subprojecta
LEFT JOIN p_project ec ON ec.id = e.projecta
LEFT JOIN p_project l ON b.parentid = l.id
LEFT JOIN p_staff m ON l.pmanagera = m.id
WHERE 1 =1
ORDER BY a.tname ASC  LIMIT 0 , 30

作者: familyX 发布时间: 2011-06-30

EXPLAIN SQL语句，索引情况如何

作者: WWWWA 发布时间: 2011-06-30

下面是explain后的情况
SQL code



id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE a ALL NULL NULL NULL NULL 7 Using temporary; Using filesort 
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.subproject 1   
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 test.a.tleader 1   
1 SIMPLE d eq_ref PRIMARY PRIMARY 4 test.b.pmanagera 1   
1 SIMPLE e ALL NULL NULL NULL NULL 34   
1 SIMPLE g eq_ref PRIMARY PRIMARY 2 test.a.tlocation 1   
1 SIMPLE h ALL NULL NULL NULL NULL 34   
1 SIMPLE k eq_ref PRIMARY PRIMARY 4 test.h.teama 1 Using index 
1 SIMPLE i eq_ref PRIMARY PRIMARY 4 test.h.subprojecta 1 Using index 
1 SIMPLE j eq_ref PRIMARY PRIMARY 4 test.h.projecta 1 Using index 
1 SIMPLE ea eq_ref PRIMARY PRIMARY 4 test.e.teama 1 Using index 
1 SIMPLE eb eq_ref PRIMARY PRIMARY 4 test.e.subprojecta 1 Using index 
1 SIMPLE ec eq_ref PRIMARY PRIMARY 4 test.e.projecta 1 Using index 
1 SIMPLE l eq_ref PRIMARY PRIMARY 4 test.b.parentid 1   
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 test.l.pmanagera 1