How can I speed up my Moodle mysql query with multiple self joins and index

Question

I have created a query with multiple joins for data fetching from multiple tables. For 5000+ records it is working very slow. I had checked and indexes are properly set up for each table used in a query. I tried to optimize this query with multiple options but it did not work and I do not exactly understand EXPLAIN or 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 table

Perhaps this question is better suited for https://codereview.stackexchange.com its seems a little off topic or broad here. — Jon, Dec 08 '17 at 15:00
It's great that you've provided the EXPLAIN, but we need the basics too. See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) - and have you redacted some text there? It's hard to tell, but unhelpful if you have. — Strawberry, Dec 08 '17 at 15:02
I would like to share other details like if you want to see table structure or other details. Actually, I didn't use EXPLAIN before and try to understand where exactly it's points to me. — user2791498, Dec 08 '17 at 17:59
Please provide `SHOW CREATE TABLE mdl_scorm_scoes_track` -- That table seems to have the biggest "rows" counts. — Rick James, Dec 09 '17 at 20:47
Is it joining to mdl_scorm_scoes_track the _same_ way multiple times? That is likely to be a big issue. — Rick James, Dec 09 '17 at 20:49
Is there only one row with `st3.element LIKE '%cmi.core.total_time%'`? (etc) — Rick James, Dec 09 '17 at 20:50
Thanks for your response. I have added 'mdl_scorm_scoes_track' Show create table detail. No, it is a multiple raw based on user and his attempted SCORM module. — user2791498, Dec 10 '17 at 19:37
Yes, I have joined 'mdl_scorm_scoes_track' table multiple times as based on elements and user attempts I need other element details for user i.e.cmi.core.total_time, cmi.core.lesson_status, cmi.core.score.raw — user2791498, Dec 10 '17 at 19:41
Hi. Read & act on [mcve]. Eg we need to know PKs/FKs/UNIQUEs/indexes of all tables (among other things). Also setting & reading the same variable in the same select statement is undefined behaviour in MySQL. PS Please look at your formatted query below your edit box before you post--your code is not formatted correctly. Also please [use text, not images/links, for text (including tables)](https://meta.stackoverflow.com/q/285551/3404097). — philipxy, Dec 11 '17 at 10:10
Sorry I am new in stackoverflow and don't know what is better way to post images. Only issue is 'mdl_scorm_scoes_track' contains more than 14,00,000 row's and cause to slow performance of the query execution. What is better approach to increase speed of mysql for large table? — user2791498, Dec 11 '17 at 10:52
Hi. Again: don't use images that are text. Cut and paste the text & align the columns in code block format. Input/output is better as a table that is also an SQL `values` so it is executable. You probably could have googled that instead of saying you don't know how. Where is your `explain` output etc? It doesn't matter if it's new, google the manual, run it now. Give as text table. Also give table statistics. GIve as much of a [mcve] as you can. If you really want help, write a procedure to generate data. Google SO for examples of & comments on better questions re slow (etc) queries. Act. — philipxy, Dec 12 '17 at 00:32

philipxy · Answer 1 · 2017-12-12T02:37:39.877

The purpose of your 4-way self-join seems roughly speaking to identify 4 rows involving the same userid-scormid-scoid-attempt but with 4 different elements. That is a costly way to get 4-tuples that appear with 4 elements. (Moreover those 5 columns form a unique key.) Read about relational division, which finds rows with subrow values that appear with all subrow values in another table, and expressing it in SQL. Eg you want groups per 4-tuple having distinct element count = 4 where element is among the 4 values.

score 0 · Answer 2 · answered Dec 12 '17 at 12:37

Yes, Your assumption is correct. Following is my light version of query. This is 4-way self join but with different elements. This is standard table and stored 14,00,000+ records so it is very difficult for me to change table structure or insert query.

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

I have also tried your suggested approach, but it was not helpful. Please check and let me know if anything is wrong in my query

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' 
)

How can I speed up my Moodle mysql query with multiple self joins and index

QUERY

mdl_scorm_scoes_track table

2 Answers2