rewriting mysql subquery that is in the from statement

Question

This query is complex but all the individual parts work.

The query gets all the users from the users table that match the client id passed in (there is a user/client link table)

The challenging part is the middle part that gets the details of which SCORM courses the user has completed (tracking data that is passed or completed). But this scorm tracking table does not hold data about the course that the scorm object is in, so therefore I have to work out the course, the scorm packages in the course, the sco objects within the scorm package in order to successfully determine whether the user has passed the course.

This used to be about 15 queries all inside loop after nested loop in PHP and then stored as arrays in RAM before spitting out the the page/download csv. This is really slow and times out for the number of users now running.

So I have to find a better solution.

I have done 4 other query pages like this--this is the last one that defeats me because it is a SUMMARY, ie it counts how many courses they have completed, and will then compare against how many they have been allocated and give us a dashboard.

It is working except for u.id reference in sub sub query.

SELECT DISTINCT u.id, u.firstname, u.lastname, u.username,

/* this part of the query gets the number of COMPLETED courses only   */
(SELECT COUNT(CASE WHEN refname=1  THEN 1 ELSE NULL END) AS complete  
FROM
    (SELECT CASE
    /* first check if the number of completions in the scorm tracking table is zero */
    WHEN COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status' AND (t1.value = 'passed' OR t1.value = 'completed') THEN 1 ELSE NULL END) = 0 THEN 0 
    /* then do the original mod */
        WHEN (MOD(COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status' AND (t1.value = 'passed' OR t1.value = 'completed') THEN 1 ELSE NULL END),
            (SELECT COUNT(sc9.id) AS scoes FROM mdl_scorm AS s9 LEFT JOIN mdl_scorm_scoes AS sc9 ON s9.id = sc9.scorm WHERE s9.course = c1.id AND sc9.launch = ''))) > 0 THEN 0
        ELSE 1
    END AS refname
    FROM mdl_user AS u1
    LEFT JOIN mdl_scorm_scoes_track AS t1 ON t1.userid = u1.id
    LEFT JOIN mdl_scorm AS s1  ON s1.id = t1.scormid
    LEFT JOIN mdl_course AS c1 ON c1.id = s1.course
    LEFT JOIN mdl_scorm_scoes AS sc1 ON s1.id = sc1.scorm
    WHERE u1.id = u.id 
    /* **this u.id is part of the OUTERMOST QUERY AND ISN'T SEEN BY THE INNER MOST NEST**    */
    AND sc1.launch = ''    
    GROUP BY c1.id, t1.attempt) AS tblname) 
AS complete,

/* this counts how many courses user has been allocated */
(SELECT COUNT(DISTINCT CONCAT(s2.course, t2.attempt)) AS allocated
FROM mdl_scorm AS s2
LEFT JOIN mdl_scorm_scoes_track AS t2 ON s2.id = t2.scormid
LEFT JOIN mdl_scorm_scoes AS sc2 ON sc2.id = t2.scoid
WHERE t2.userid = u.id),

sfhuc.clientid

FROM mdl_user AS u
LEFT JOIN mdl_sfh_user_client AS sfhuc ON sfhuc.userid = u.id
LEFT JOIN mdl_user_info_data ud_employeeid ON ud_employeeid.userid = u.id AND ud_employeeid.fieldid = 1 
LEFT JOIN mdl_user_info_data ud_jobdescription ON ud_jobdescription.userid = u.id AND ud_jobdescription.fieldid = 3
LEFT JOIN mdl_sfh_client AS sfhc ON sfhc.id = sfhuc.clientid
LEFT JOIN mdl_scorm_scoes_track AS t ON t.userid = u.id

WHERE 1=1
AND u.deleted = 0
AND u.timecreated > 0
AND sfhc.id = 2076441
GROUP BY u.id
ORDER BY u.lastname, u.firstname

Answer 1

• I don't see any subqueries in FROM (aka "derived table").

• There are tables where you reference only one column; see if the table references can be removed:

    sfhc:  (id)
    t:  (userid)
    sc2:  (id)
    c1:  (id)
    u1:  (id)
  

• Shortening the code can help readability: (Note a Boolean value is treated as 1 (true) or 0 (false), making SUM work.)

    COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status'
        AND  (t1.value = 'passed'
          OR  t1.value = 'completed')
       THEN 1 ELSE NULL END
  
    -->
  
    SUM( t1.element = 'cmi.core.lesson_status' AND
         t1.value IN ('passed', 'completed') )
  

• CONCAT is unnecessary:

    COUNT(DISTINCT CONCAT(s2.course, t2.attempt))
  
    -->
  
    COUNT(DISTINCT s2.course, t2.attempt)
  

• These seem to be totally useless:

    LEFT JOIN  mdl_user_info_data ud_employeeid
          ON  ud_employeeid.userid = u.id
         AND  ud_employeeid.fieldid = 1
  
    LEFT JOIN  mdl_user_info_data ud_jobdescription
          ON  ud_jobdescription.userid = u.id
         AND  ud_jobdescription.fieldid = 3
  

• Possible indexes:

    sfhuc:  (userid, clientid)
    t2:  (userid, scoid, scormid, attempt)
    t1:  (attempt, scormid, userid, value, element)
    mdl_scorm_scoes:  (launch, scorm)
  

None of these necessarily 'solve' your problem, but the main point to areas that need to be investigated or re-thought.