56,677
社区成员
发帖
与我相关
我的任务
分享
SELECT
k.resource_id,
k.resource_type,
(k.resource_type%2) resOffset,
z.type_index,
c.question_id resourceId,
k.resource_name resoutceName,
l.erStatus erStatus,
COALESCE(j.is_recommend,0) isRecommend,
if (ADDDATE(MAX(j.insert_time),365) > NOW(),1,0) isAdd
FROM t_user_question c
JOIN (
#问题1:性能与该子查询返回记录数成反比(一条记录100%,两条200% T_T````),这太恐怖了
select
c.question_id ,
ABS(d.question_state) erStatus
from t_class_homework a
JOIN t_user_homework b on a.homework_id = b.homework_id
JOIN t_user_question c on a.homework_id = c.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on f.class_id = a.class_id and f.user_id = c.user_id
WHERE f.class_id = 1001
and f.user_id = 10001
and b.state in (2,3)
and a.homework_id = 2
and b.user_id= f.user_id
GROUP BY c.question_id
) l on l.question_id = c.question_id
JOIN t_user_homework h on c.homework_id = h.homework_id
JOIN t_user_question_detail d on c.user_question_id = d.user_question_id
JOIN t_user_class f on c.user_id = f.user_id
JOIN t_user g on c.user_id = g.user_id
#问题2:下面一句大概影三分之二的性能,对于这个查询只有8条匹配数据 ,为什么影响如此之大
LEFT JOIN t_question_user_recommen j on j.question_id = c.question_id
JOIN t_question k on k.question_id = l.question_id
JOIN t_subject_question_type z on z.question_type_id = k.question_type_id and z.subject_id = k.subject_id
WHERE f.class_id = 1001
and c.homework_id = 2
and h.state in (2,3)
AND h.user_id = f.user_id
GROUP BY c.question_id