mysql 百万级数据存储过程有什么优化方法吗

[qq_23141709]

DROP PROCEDURE IF EXISTS `proc_battleinfo`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_battleinfo`(IN userid INT,IN gid INT)
BEGIN
SET NAMES utf8mb4;
SELECT drawid,roomid,bureau,gameplayer,basescore,playmethod,
max(case when chairid=0 THEN UNIX_TIMESTAMP(concludetime) END) AS concludetime,
max(case when chairid=0 THEN nickname END) AS na0,
max(case when chairid=1 THEN nickname END) AS na1,
max(case when chairid=2 THEN nickname END) AS na2,
max(case when chairid=3 THEN nickname END) AS na3,
max(case when chairid=0 THEN id END) AS uid0,
max(case when chairid=1 THEN id END) AS uid1,
max(case when chairid=2 THEN id END) AS uid2,
max(case when chairid=3 THEN id END) AS uid3,
sum(case when chairid=0 THEN score END) AS score0,
sum(case when chairid=1 THEN score END) AS score1,
sum(case when chairid=2 THEN score END) AS score2,
sum(case when chairid=3 THEN score END) AS score3,
max(case when chairid=0 THEN CONCAT(UNIX_TIMESTAMP(starttime),gid,roomid) END) AS replay
FROM (SELECT a.drawid,b.id,b.chairid,b.score,b.starttime,b.concludetime,b.roomid,b.nickname,b.bureau,b.gameplayer,b.basescore,b.playmethod
FROM (SELECT distinct drawid FROM draw_score WHERE id=userid AND gameid = gid
AND concludetime >= date_sub(NOW(),INTERVAL 48 HOUR)) AS a left join draw_score b
ON b.drawid = a.drawid) AS aa GROUP BY drawid ORDER BY drawid DESC limit 10;

END
;;

DELIMITER ;






各位大神，看看这个存储过程能优化吗

[qq_23141709]

已经解决了，不需要优化存储过程，为表建立索引就可以极大的优化查询速度了


这是建立索引后的explain

[卖水果的net]

楼主自已解决了问题，赞一个； 希望楼主常来论坛，帮助其他的网友解决问题。

[ChinaITOldMan]

楼上牛人多来指点下。

[zjcxc]

原来索引都没有嗦

[小野马1209]

建立合适的索引确实查询效率可以加快很多，不然要全表进行检索