Блог

У меня есть этот запрос mysql :

 SELECT tbl.userid, tbl.name, tbl.courseid, tbl.fullname, IFNULL(tbl.instance, '-') instance, IFNULL(tbl.activityname, '-') activityname,
    IFNULL(tbl.module, '-') module, IFNULL(tbl.attempt, '-') attempt, IFNULL(tbl.score, '-') score,
    CASE tbl.module WHEN 'quiz' THEN (
        CASE WHEN (tbl.attempt IS NULL AND tbl.score IS NULL) THEN 'NOT YET'
        WHEN (tbl.attempt IS NOT NULL AND tbl.score IS NULL) THEN 'ATTEMPT'
        WHEN (tbl.attempt IS NOT NULL AND tbl.score IS NOT NULL) THEN 'FINISH'
        END)
    WHEN 'scorm' THEN (
        CASE WHEN tbl.attempt IS NULL THEN 'NOT YET'
        ELSE (SELECT `status` FROM mdl_scorm_logs WHERE scormid = tbl.instance AND userid = tbl.userid)
        END)
    ELSE '-'
    END `status`
FROM (
    SELECT tbl1.userid, tbl1.name, tbl1.courseid, tbl1.fullname, tbl2.instance,
        CASE tbl2.module WHEN 'quiz' THEN (SELECT `name` FROM mdl_quiz WHERE id = tbl2.instance AND course = tbl1.courseid)
        WHEN 'scorm' THEN (SELECT `name` FROM mdl_scorm WHERE id = tbl2.instance AND course = tbl1.courseid)
        END activityname
        , tbl2.module, 
        CASE tbl2.module WHEN 'quiz' THEN (SELECT MAX(attempt) FROM mdl_quiz_attempts WHERE quiz = tbl2.instance AND userid = tbl1.userid)
        WHEN 'scorm' THEN (SELECT MAX(attempt) FROM mdl_scorm_scoes_track WHERE scormid = tbl2.instance AND userid = tbl1.userid )
        END attempt,
        (SELECT CONCAT(TRUNCATE(ROUND(gg.finalgrade / gi.grademax * 100, 2), 2), ' %')
        FROM mdl_grade_grades gg
        JOIN mdl_grade_items gi ON gg.itemid = gi.id
        WHERE gg.userid = tbl1.userid AND gi.courseid = tbl1.courseid AND gi.itemname = activityname AND gi.itemtype = 'mod'
            AND gi.itemmodule = tbl2.module AND gi.iteminstance = tbl2.instance) score
    FROM (
        SELECT u.id userid, CONCAT(u.firstname, ' ', u.lastname) `name`, c.id courseid, c.fullname
        FROM mdl_user u
        JOIN mdl_role_assignments ra ON u.id = ra.userid
        JOIN mdl_context co ON ra.contextid = co.id
        JOIN mdl_role r ON ra.roleid = r.id
        JOIN mdl_course c ON co.instanceid = c.id
        JOIN mdl_enrol e ON c.id = e.courseid
        JOIN mdl_user_enrolments ue ON e.id = ue.enrolid AND u.id = ue.userid
        WHERE r.archetype = 'student') tbl1
    LEFT JOIN (
        SELECT cm.course, m.name module, cm.instance FROM mdl_course_modules cm
        JOIN mdl_modules m ON cm.module = m.id
        WHERE m.name IN ('quiz', 'scorm')
        ) tbl2
    ON tbl1.courseid = tbl2.course) tbl
WHERE tbl.userid = '4' AND tbl.courseid = '8'
GROUP BY tbl.courseid, tbl.instance
ORDER BY tbl.name, tbl.fullname, tbl.activityname
  

Результат после того, как я использую EXPLAIN :

 id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived3>  ALL                 122 Using where; Using temporary; Using filesort
3   DERIVED <derived9>  ALL                 42  
3   DERIVED <derived10> ALL                 23  
10  DERIVED m   range   PRIMARY,mdl_modu_nam_ix mdl_modu_nam_ix 62      2   Using where; Using index
10  DERIVED cm  ref mdl_courmodu_mod_ix mdl_courmodu_mod_ix 8   moodle.m.id 4   
9   DERIVED r   ref PRIMARY,mdl_role_arc_ix mdl_role_arc_ix 92      1   Using where; Using index
9   DERIVED ra  ref mdl_roleassi_rol_ix,mdl_roleassi_con_ix,mdl_roleassi_use_ix mdl_roleassi_rol_ix 8   moodle.r.id 5   
9   DERIVED u   eq_ref  PRIMARY PRIMARY 8   moodle.ra.userid    1   
9   DERIVED co  eq_ref  PRIMARY,mdl_cont_ins_ix PRIMARY 8   moodle.ra.contextid 1   
9   DERIVED e   ref PRIMARY,mdl_enro_cou_ix mdl_enro_cou_ix 8   moodle.co.instanceid    1   Using index
9   DERIVED c   eq_ref  PRIMARY PRIMARY 8   moodle.co.instanceid    1   
9   DERIVED ue  eq_ref  mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix mdl_userenro_enruse_uix 16  moodle.e.id,moodle.ra.userid    1   Using index
8   DEPENDENT SUBQUERY  gi  ref PRIMARY,mdl_graditem_itenee_ix,mdl_graditem_cou_ix  mdl_graditem_cou_ix 9   tbl1.courseid   1   Using where
8   DEPENDENT SUBQUERY  gg  eq_ref  mdl_gradgrad_useite_uix,mdl_gradgrad_ite_ix,mdl_gradgrad_use_ix mdl_gradgrad_useite_uix 16  tbl1.userid,moodle.gi.id    1   Using where
7   DEPENDENT SUBQUERY  mdl_scorm_scoes_track   ref mdl_scorscoetrac_usescosco_uix,mdl_scorscoetrac_use_ix,mdl_scorscoetrac_sco_ix  mdl_scorscoetrac_usescosco_uix  16  tbl1.userid,tbl2.instance   6   Using where; Using index
6   DEPENDENT SUBQUERY  mdl_quiz_attempts   ref mdl_quizatte_use_ix,mdl_quizatte_qui_ix mdl_quizatte_qui_ix 8   tbl2.instance   2   Using where
5   DEPENDENT SUBQUERY  mdl_scorm   eq_ref  PRIMARY,mdl_scor_cou_ix PRIMARY 8   tbl2.instance   1   Using where
4   DEPENDENT SUBQUERY  mdl_quiz    eq_ref  PRIMARY,mdl_quiz_cou_ix PRIMARY 8   tbl2.instance   1   Using where
2   DEPENDENT SUBQUERY  mdl_scorm_logs  eq_ref  PRIMARY PRIMARY 16  func,func   1   Using where
  

Есть идеи по его оптимизации?
Спасибо.

БОЖЕ. Денормализация — единственный способ справиться с этой ситуацией. Хорошо, вы оптимизируете этот запрос. Что вы будете делать в следующий раз, когда вам придется объединить 15 таблиц в одном запросе?

Ваш запрос выглядит как отчет. Вы должны спроектировать свою базу данных для создания отчетов. Ознакомьтесь со следующими ссылками, это может быть вам полезно.

http://en.wikipedia.org/wiki/Denormalization

http://en.wikipedia.org/wiki/Star_schema

ДА. Преобразуйте соответствующие подзапросы во внешние соединения. Вероятно, вы сможете хорошо начать, запросив эти термины на этом сайте и в Google.

Я полагаю, то же самое для «производных таблиц».

Синтаксис

ВЫБЕРИТЕ … FROM (ВЫБРАТЬ ИЗ … ) может быть проблематичным.

Возможно, я не понимаю контекста, но денормализация кажется мне крайне преждевременным выбором.