Как я могу ускорить мой Moodle MySQL запрос с несколькими самообъединениями и индексами

Question

Я создал запрос с несколькими объединениями для извлечения данных из нескольких таблиц. Для 5000+ записей это работает очень медленно. Я проверил, и индексы правильно настроены для каждой таблицы, используемой в запросе. Я пытался оптимизировать этот запрос с несколькими вариантами, но он не работал, и я не совсем понимаю, EXPLAIN или EXPLAIN EXTENDED Help.

=====

QUERY

EXPLAIN EXTENDED
SELECT @rownum := @rownum + 1 AS id,
       u.id AS userid,
       u.username AS employeeid,
       u.firstname,
       u.lastname,
       u.email AS email,
       u.city AS state,
       c.fullname AS course,
       c.id AS courseid,
       c.fullname AS coursename,
       fi2.data branchid,
       fi3.data branchname,
       fi6.data areaname,
       fi7.data regname,
       fi8.data designation,
       fi9.data department,
       fi10.data zone,
       fi11.data branchcategory,
       fi12.data branchdistrict,
       fi13.data branchstate,
       fi17.data gender,
       fi21.data employeecategory,
       fi22.data persontype,
       fi23.data assignmentstatus,
       fi30.data POSITION,
       fi32.data dateofjoining,
       fi33.data dateofbirth,
       st.scoid,
       st.scormid,
       st.attempt,
       st.value cmistarttime,
       st5.value cmilessonstatus,
       st5.timemodified cmitimemodified,
       st3.value cmitotaltime,
       st2.value cmiscore
FROM (
       SELECT @rownum := 0
     ) r,
     mdl_user u
     JOIN mdl_role_assignments ra ON ra.userid = u.id
     JOIN mdl_context ctx ON ctx.id = ra.contextid
     JOIN mdl_course c ON c.id = ctx.instanceid
     JOIN mdl_scorm s ON s.course = c.id
     JOIN mdl_scorm_scoes_track st ON st.scormid = s.id AND st.userid = u.id
     JOIN mdl_scorm_scoes_track AS st2 ON (u.id = st2.userid AND st2.scormid
       = s.id AND st2.scoid = st.scoid AND st2.attempt = st.attempt)
     JOIN mdl_scorm_scoes_track AS st3 ON (u.id = st3.userid AND st3.scormid
       = s.id AND st3.scoid = st.scoid AND st3.attempt = st.attempt)
     JOIN mdl_scorm_scoes_track AS st5 ON (u.id = st5.userid AND st5.scormid
       = s.id AND st5.scoid = st.scoid AND st5.attempt = st.attempt)
     JOIN mdl_user_info_data AS fi2 ON u.id = fi2.userid
     JOIN mdl_user_info_data AS fi3 ON u.id = fi3.userid
     JOIN mdl_user_info_data AS fi6 ON u.id = fi6.userid
     JOIN mdl_user_info_data AS fi7 ON u.id = fi7.userid
     JOIN mdl_user_info_data AS fi8 ON u.id = fi8.userid
     JOIN mdl_user_info_data AS fi9 ON u.id = fi9.userid
     JOIN mdl_user_info_data AS fi10 ON u.id = fi10.userid
     JOIN mdl_user_info_data AS fi11 ON u.id = fi11.userid
     JOIN mdl_user_info_data AS fi12 ON u.id = fi12.userid
     JOIN mdl_user_info_data AS fi13 ON u.id = fi13.userid
     JOIN mdl_user_info_data AS fi17 ON u.id = fi17.userid
     JOIN mdl_user_info_data AS fi21 ON u.id = fi21.userid
     JOIN mdl_user_info_data AS fi22 ON u.id = fi22.userid
     JOIN mdl_user_info_data AS fi23 ON u.id = fi23.userid
     JOIN mdl_user_info_data AS fi30 ON u.id = fi30.userid
     JOIN mdl_user_info_data AS fi32 ON u.id = fi32.userid
     JOIN mdl_user_info_data AS fi33 ON u.id = fi33.userid
WHERE c.id > 0 AND
      u.id > 0 AND
      u.deleted = 0 AND
      u.suspended = 0 AND
      u.confirmed = 1 AND
      u.id <= 1000 AND
      c.visible = 1 AND
      st.element LIKE '%x.start.time%' AND
      st2.element LIKE '%cmi.core.score.raw%' AND
      st3.element LIKE '%cmi.core.total_time%' AND
      st5.element LIKE '%cmi.core.lesson_status%' AND
      fi2.fieldid = 2 AND
      fi3.fieldid = 3 AND
      fi6.fieldid = 6 AND
      fi7.fieldid = 7 AND
      fi8.fieldid = 8 AND
      fi9.fieldid = 9 AND
      fi10.fieldid = 10 AND
      fi11.fieldid = 11 AND
      fi12.fieldid = 12 AND
      fi13.fieldid = 13 AND
      fi17.fieldid = 17 AND
      fi21.fieldid = 21 AND
      fi22.fieldid = 22 AND
      fi23.fieldid = 23 AND
      fi30.fieldid = 30 AND
      fi32.fieldid = 32 AND
      fi33.fieldid = 33 AND
      ra.roleid = 5 AND
      ctx.contextlevel = 50

==========================

таблица mdl_scorm_scoes_track

таблица mdl_scorm_scoes_track

Answer 1

Да, ваше предположение верно. Ниже приводится моя облегченная версия запроса. Это 4-х стороннее самостоятельное соединение, но с разными элементами. Это стандартная таблица и хранится более 14 000 записей, поэтому мне очень трудно изменить структуру таблицы или вставить запрос.

SELECT DISTINCT st1.* FROM mdl_scorm_scoes_track st1
JOIN mdl_scorm_scoes_track st2 ON st1.userid = st2.userid AND st1.scormid = st2.scormid AND st1.scoid = st2.scoid AND st1.attempt = st2.attempt
JOIN mdl_scorm_scoes_track st3 ON st1.userid = st3.userid AND st1.scormid = st3.scormid AND st1.scoid = st3.scoid AND st1.attempt = st3.attempt
JOIN mdl_scorm_scoes_track st4 ON st1.userid = st4.userid AND st1.scormid = st4.scormid AND st1.scoid = st4.scoid AND st1.attempt = st4.attempt
AND st1.element = 'x.start.time' 
AND st2.element = 'cmi.core.score.raw'
AND st3.element = 'cmi.core.total_time'
AND st4.element = 'cmi.core.lesson_status' AND st1.userid <= 10
GROUP BY st1.userid, st1.scormid, st1.scoid, st1.attempt

Я также попробовал ваш предложенный подход, но он не помог. Пожалуйста, проверьте и дайте мне знать, если что-то не так в моем запросе

SELECT DISTINCT st1.* FROM mdl_scorm_scoes_track st1
JOIN mdl_scorm_scoes_track st2 ON st1.userid = st2.userid AND st1.scormid = st2.scormid AND st1.scoid = st2.scoid AND st1.attempt = st2.attempt
JOIN mdl_scorm_scoes_track st3 ON st1.userid = st3.userid AND st1.scormid = st3.scormid AND st1.scoid = st3.scoid AND st1.attempt = st3.attempt
JOIN mdl_scorm_scoes_track st4 ON st1.userid = st4.userid AND st1.scormid = st4.scormid AND st1.scoid = st4.scoid AND st1.attempt = st4.attempt
AND st1.element = 'x.start.time' 
AND st2.element = 'cmi.core.score.raw'
AND st3.element = 'cmi.core.total_time'
AND st4.element = 'cmi.core.lesson_status' AND st1.userid <= 10
GROUP BY st1.userid, st1.scormid, st1.scoid, st1.attempt
HAVING COUNT(*) = (
  SELECT COUNT(*) FROM mdl_scorm_scoes_track st5
  WHERE st1.userid = st5.userid AND st1.scormid = st5.scormid AND st1.scoid = st5.scoid AND st1.attempt = st5.attempt AND st5.element = 'x.start.time' 
)

Answer 2

Цель вашего четырехстороннего самостоятельного объединения, по-видимому, грубо говоря - идентифицировать 4 строки, включающие одну и ту же переменную userid-scormid-scoid-попытку, но с 4 различными элементами. Это дорогостоящий способ получить 4 кортежа, которые появляются с 4 элементами. (Более того, эти 5 столбцов образуют уникальный ключ.) Читайте о реляционном делении, которое находит строки со значениями вложенных строк, которые отображаются со всеми значениями вложенных строк в другой таблице, и выражает их в SQL. Например, вы хотите, чтобы группы на 4 кортежа имели различное количество элементов = 4, где элемент находится среди 4 значений.