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 ;
各位大神,看看这个存储过程能优化吗
5 个解决方案
#1
已经解决了,不需要优化存储过程,为表建立索引就可以极大的优化查询速度了
这是建立索引后的explain
这是建立索引后的explain
#2
楼主自已解决了问题,赞一个;
希望楼主常来论坛,帮助其他的网友解决问题。
希望楼主常来论坛,帮助其他的网友解决问题。
#3
楼上牛人多来指点下。
#4
原来索引都没有嗦
#5
建立合适的索引确实查询效率可以加快很多,不然要全表进行检索
#1
已经解决了,不需要优化存储过程,为表建立索引就可以极大的优化查询速度了
这是建立索引后的explain
这是建立索引后的explain
#2
楼主自已解决了问题,赞一个;
希望楼主常来论坛,帮助其他的网友解决问题。
希望楼主常来论坛,帮助其他的网友解决问题。
#3
楼上牛人多来指点下。
#4
原来索引都没有嗦
#5
建立合适的索引确实查询效率可以加快很多,不然要全表进行检索