mysql数据库8W+数据查询需要两秒多

Question

SELECT a.id,a.serialNumber,a.nickName,a.latitude,a.longitude,a.online,a.alarmStatus,IFNULL(b.groupId,-1) groupId
FROM t_lamp a
LEFT JOIN t_lamp_group_relation b ON a.id = b.lampId
LEFT JOIN t_loop_line c ON a.lineId = c.id
WHERE a.projectId = 10018

请问这条sql可不可以优化啊 8W多数据查询要两秒多

Answer 1

//字段为了省事用 * 了
select * from
(select * from t_lamp l where l.projectId = 10018 ) a
LEFT JOIN t_lamp_group_relation b ON a.id = b.lampId
LEFT JOIN t_loop_line c ON a.lineId = c.id

projectId 加个索引，如果是主键就不用建
同理 lampId 加个索引，如果是主键就不用建