优化sql语句问题 求指教
select id,createTime,complainant,updateTime, complainantType, w.workerName creatorName, content,complaintType,status,violation,priority,isDuplicate,complaintClass,
(select GROUP_CONCAT(ifnull(t2.deptName,''),' ',ifnull(t3.groupName,''),' ',ifnull(t4.workerName,''),' (',ifnull(t4.workerId,t1.workerId),')' SEPARATOR '<br>') from ComplaintPrincipal t1 left join Dept t2 on t2.deptId=t1.deptId left join WorkerGroup t3 on t3.groupId=t1.groupId left join Worker t4 on t4.workerId=t1.workerId where t1.complaintId=c.id group by t1.complaintId ) deptgroupworker
from Complaint c left join Worker w on w.workerId = c.creator where 1=1 and createTime>='2011-09-08' and createTime<'2012-06-15' + interval 1 day and (status > 0 or (status=0 and creator=110)) order by updateTime desc limit 0,5000;
此语句查询需要4秒多 Complaint表只有14000多的记录 ComplaintPrincipal表不到一万的记录 其他表不超过三千的记录
把content字段去掉时 耗时不到一秒 此字段定义如下
content` varchar(3000) NOT NULL COMMENT
deptId workerid grouid complaintid createtime creator updatetime 均已建立索引
求高手指导如何优化
[解决办法]
io问题 优化可能不大
[解决办法]
那就不要content这个字段 然后拿id单独再去取这5000个content
