SQL 查询如何优化
我的机器环境（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, CASE WHEN ul.lastaccess>curdate() THEN 1 WHEN (ul.lastaccess>subdate(curdate(),1)
and ul.lastaccess<curdate()) THEN 2 WHEN (ul.lastaccess>date_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 from UserSessionLog ul,UserEntity uwhere u.id = ul.userid gro
up by dateInterval; 检查是否有索引
关联字段数据类型应该一样
把表UserEntity前置试试
简化逻辑，尽量不要用distinct
用explain分析优化
.... add the column
页:
[1]