1.先丢查询语句
-- 业务:查询某个学校[学校id为120082]的某一节课[大学英语]有多少学生选SELECT COUNT(DISTINCT _choosemate.studentId)
FROM _choosemate LEFT JOIN _course ON _course.id = _choosemate.courseId
LEFT JOIN _student ON _choosemate.studentId = _student.id
WHERE _course.schoolId = 120082 AND _course.`name` = '大学英语'
AND _course.beginYear = 2013 AND term = 1 AND LENGTH(_student.nickName) > 02.问题
单独执行时速度挺快的,但当并发量大时速度变得很慢,
被这个问题困扰很久了,急求大虾们指教!!!
-- 业务:查询某个学校[学校id为120082]的某一节课[大学英语]有多少学生选SELECT COUNT(DISTINCT _choosemate.studentId)
FROM _choosemate LEFT JOIN _course ON _course.id = _choosemate.courseId
LEFT JOIN _student ON _choosemate.studentId = _student.id
WHERE _course.schoolId = 120082 AND _course.`name` = '大学英语'
AND _course.beginYear = 2013 AND term = 1 AND LENGTH(_student.nickName) > 02.问题
单独执行时速度挺快的,但当并发量大时速度变得很慢,
被这个问题困扰很久了,急求大虾们指教!!!
create index xx2 on _student(id,term,nickName)
create index xx3 on _choosemate(courseId)
1.使用LEFT JOIN没什么道理 换成INNER JOIN会优化一点
2.如果不使用AND LENGTH(_student.nickName) > 0这个条件 就不需要连接_student表了 会优化很多 但是这要从业务逻辑上考虑