我的机器环境(linux+mysql5.0.4+java),最近发现以下2条语句导致系统变慢
UserSessionLog 有6267689条记录 ,UserEntity 有5万条,
Query_time高达53秒,问原因(group by),如何优化
# Query_time: 53Lock_time: 0Rows_sent: 20Rows_examined: 6267689
select
log.userId, max(log.lastLoginTime) lastLoginTime, count(1) + 1 nrOfLogins,
u.partnerId,u.userName from
UserSessionLog log, UserEntity uwhere
log.userId= u.id group by log.userId
order by lastLoginTime desc li
mit 20;
Query_time高达57秒,问原因(group by),如何优化
# Query_time: 57Lock_time: 0Rows_sent: 5Rows_examined: 10619215
SET timestamp=1241525924;
selectcount(distinct(ul.userId)) number,CASEWHEN ul.lastaccess>curdate() THEN 1 WHEN
(ul.lastaccess>subdate(curdate(),1)
and ul.lastaccessdate_Sub(curdate(), INTERVAL DAYOFMONTH
(curdate())-1 day)) THEN 3
WHEN (ul.lastaccess>date_sub(date_Sub(curdate(), INTERVAL DAYOFMONTH(curdate())-1 day),INTERVAL 1 month)
and ul.lastaccess<DATE_SUB(sys
date(),INTERVAL 1 month)) THEN 4 ELSE 5END as dateInterval fromUserSessionLog ul,UserEntity u
where u.id = ul.userid gro
up by dateInterval;
|