表book
id,name
1 三国演义
表bookcontent
id,bookid,title
1 1 第一节
2 1 第二节
3 1 第三节
把书按自增ID排序并调列出书的最新章节
select * FROM book as a left join bookcontent as b
on b.id =(select max(id) from bookcontent where bookid=a.id)
order by a.id desc limit 10现在这个有点慢,怎么优化下
id,name
1 三国演义
表bookcontent
id,bookid,title
1 1 第一节
2 1 第二节
3 1 第三节
把书按自增ID排序并调列出书的最新章节
select * FROM book as a left join bookcontent as b
on b.id =(select max(id) from bookcontent where bookid=a.id)
order by a.id desc limit 10现在这个有点慢,怎么优化下
on a.id=b.id
inner join
(select bookid,max(id) as ma from bookcontent ) c
on b.id =c.ma
order by a.id desc limit 10
(select * from bookcontent as b
where not exists(select 1 from bookcontent where bookid=b.bookid and id>b.id)
) c
on a.id=c.bookid;
inner join bookcontent b
on a.id=c.bookid
inner join
(select bookid,max(id) as ma from bookcontent group by bookid) c
on b.id =c.ma and b.bookid=c.bookid
order by a.id desc limit 10
from (select bookid,max(id) as ma from bookcontent group by bookid) c
inner join bookcontent b on c.ma=b.id
right join book a on a.id=b.bookid
show index from book