如何快速查询每个分组的最新一条记录？

Question

SELECT *

FROM (SELECT id,value,create_time FROM `table_a` ORDER BY create_time DESC) test 

GROUP BY id 

ORDER BY create_time DESC 

LIMIT 0,30

SELECT a.id, a.value, a.create_time

FROM `table_a`

LEFT JOIN (SELECT id,max(create_time) AS toptime FROM `table_a` GROUP BY id) as b ON a.id=b.id

WHERE a.create_time=b.toptime

ORDER BY a.create_time DESC

LIMIT 0,30 

以上两个sql分别要9秒和6秒。共有一百多万条数据，求教怎样能够快点，需分页，最好做到1秒以内。多次查询也可以。

Answer 1

给你要作为分组条件和排序条件的字段加上索引