-- 2008-12-18 12:12:16 --
select a.msg_id,a.msg_parentid,a.msg_baseid,a.msg_title,a.msg_author,a.msg_readtimes,a.msg_time,a.msg_content_words,a.msg_content,a.msg_image from main_data a left join (select msg_baseid, msg_id from main_data where msg_boradid = 'emotion.programmer' group by msg_baseid order by msg_id desc limit 1,10) b on a.msg_baseid=b.msg_baseid where b.msg_baseid is not null order by a.msg_baseid desc, b.msg_baseid asc
--Execute Time(5.863 sec(s))--


增加msg_baseid、msg_boradid 的两个索引

-- 2008-12-18 12:25:22 --
select a.msg_id,a.msg_parentid,a.msg_baseid,a.msg_title,a.msg_author,a.msg_readtimes,a.msg_time,a.msg_content_words,a.msg_content,a.msg_image from main_data a left join (select msg_baseid, msg_id from main_data where msg_boradid = 'emotion.programmer' group by msg_baseid order by msg_id desc limit 100,10) b on a.msg_baseid=b.msg_baseid where b.msg_baseid is not null order by a.msg_baseid desc, b.msg_baseid asc
--Execute Time(5.874 sec(s))--


改变Join写法
-- 2008-12-18 12:31:39 --
select a.msg_id,a.msg_parentid,a.msg_baseid,a.msg_title,a.msg_author,a.msg_readtimes,a.msg_time,a.msg_content_words,a.msg_content,a.msg_image from main_data a inner join (select msg_baseid, msg_id from main_data where msg_boradid = 'emotion.programmer' group by msg_baseid order by msg_id desc limit 100,10) b on a.msg_baseid=b.msg_baseid order by a.msg_baseid desc, b.msg_baseid asc
--Execute Time(3.607 sec(s))--

后续有时间再试试了