多表联合查询优化

Question

explain select SQL_CALC_FOUND_ROWS * from task a,user b ,task_project c,task_platform d where a.user_id = b.id and a.project_id = c.id and c.platform_id = d.id order by a.time_created desc limit 10 offset 0;

这条语句查询要3s，如果分开来查时间加起来不超过1s，
问题好像出在 c.platform_id = d.id 与a表无关联。
求优化。

Answer 1

1. 表task建立time_created索引
2. 统计条数和分页分开计算，不使用SQL_CALC_FOUND_ROWS
3. 不使用select *，只取需要的字段

个人建议：SQL不要使用where形式的笛卡尔积连接，而是指明left join或者是inner join，这样更直观，效率貌似也比较高。
按照表名简单推断应该是要查出最近创建的10个task，改造成left join：

select *
from task a
 left join `user` b on a.user_id = b.id
 left join task_project c on a.project_id = c.id
 left join task_platform d on c.platform_id = d.id
order by a.time_created desc 
limit 10 offset 0;

如果要求a.user_id要关联到b.id,那要改成inner join。
如果没有where条件，完全可以改成如下，查询效率应该是毫秒级：

select *
from (select * from task order by time_created desc limit 10 offset 0)a
 left join `user` b on a.user_id = b.id
 left join task_project c on a.project_id = c.id
 left join task_platform d on c.platform_id = d.id
order by a.time_created desc;