如何高效返回每个infomation中info_comment得票数最多的commentContent

yongpiao

infomation信息表 字段 infoID
info_comment信息评论表   字段 commentID，infoID,commentContent
comment_vote 用户投票表    字段 commentID，infoID，voterID
如何高效返回每个infomation中info_comment得票数最多的commentContent？怎么用上索引？

yongpiao

我自己写的方法，感觉很不好：
select * from info_comment d,
(SELECT *
FROM (

SELECT *
FROM (

SELECT infoID, commentID, count( commentID ) AS commentCount
FROM `comment_vote`
GROUP BY infoID, commentID
) AS a
ORDER BY infoID, commentCount DESC
) AS b
GROUP BY infoID) as c
where d.infoID=c.infoID and d.commentID=c.commentID