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

Firstly, it is recommended to add the tbl_a table to add the index of the id1 field, and the tbl_b table to add the indexes of the id1 and id2 fields, and then check whether the processing meets the requirements.

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

At first glance, the statements for taking name and detail can be combined for query processing, such as:

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';