mysql - 一条多表联查SQL语句优化的问题

Question

SELECT *,
    (SELECT name FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as name,
    (SELECT detail FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as detail
FROM tbl_a
WHERE id1='1';

暂且无视那个*号。请问这个语句怎么优化到最佳

Answer 1

首先建议增加tbl_a表增加id1字段的索引,tbl_b表增加id1和id2字段的索引,然后看一下处理是否满足要求。

create index idx_a on tbl_a(id1);
create index idx_b on tbl_b(id1,id2);

初步看可以把取name和detail的语句合并到一起查询处理，如：

SELECT *,
    (SELECT name, detail FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1)
FROM tbl_a
WHERE id1='1';