mysql order by 查询慢

Question

mysql中 两个表left join ，order by 字段为非主表字段，导致很慢 如果换成主表的索引字段就很快

示例：

select a.c1 ,a.c2........ from tab_a a left join tab_b b on a.u_id = b.id where ................... order by b.c5 desc limit 10

其中 order by b.c5 c5字段为索引字段，但是explain后显示 主表没走索引 即使where 后加了索引条件字段 ，也是ALL

请问如何优化

Answer 1

SELECT a.c1, a.c2, ...
FROM tab_a a
LEFT JOIN (
SELECT id, c5
FROM tab_b
WHERE ...
ORDER BY c5 DESC
LIMIT 10
) b ON a.u_id = b.id
WHERE ...
ORDER BY b.c5 DESC
LIMIT 10;