Martin - the query that was performing badly (1.18s?) according to skype was this:
SELECT DISTINCT u.id, u.username, u.firstname, u.lastname, u.email, u.city, u.country, u.picture, u.lang, u.timezone, u.emailstop, u.maildisplay, u.imagealt, COALESCE( ul.timeaccess, 0 ) AS lastaccess, r.hidden, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
FROM user u
LEFT OUTER JOIN context ctx
ON ( u.id = ctx.instanceid
AND ctx.contextlevel =30 )
JOIN role_assignments r
ON u.id = r.userid
LEFT OUTER JOIN user_lastaccess ul
ON ( r.userid = ul.userid
AND ul.courseid =5 )
WHERE (
r.contextid =53
OR r.contextid
IN ( 54, 50 )
)
AND u.deleted =0
AND (
ul.courseid =5
OR ul.courseid IS NULL
)
AND u.username != 'guest'
AND r.roleid NOT
IN ( 1, 2 )
ORDER BY lastaccess DESC
LIMIT 20
According to your notes, removing DISTINCT made it drop to ~0.58s. And then removing the left outer join to about 0.40s.
I've rewritten it below to use a subselect where we apply a much smarter GROUP BY which fixes a bug we had before (hidden/unhidden role-assignments would mean that a user was listed twice) – can you tell me if it compares well?
SELECT u.id, u.username, u.firstname, u.lastname, u.email, u.city, u.country, u.picture, u.lang, u.timezone, u.emailstop, u.maildisplay, u.imagealt, COALESCE( ul.timeaccess, 0 ) AS lastaccess, eu.hidden, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
FROM user u
JOIN (SELECT iu.id, MIN(r.hidden) AS hidden
FROM user iu
JOIN role_assignments r
ON iu.id = r.userid
WHERE ( r.contextid =53
OR r.contextid IN ( 54, 50 ) )
AND r.roleid NOT
IN ( 1, 2 )
GROUP BY iu.id
) eu
ON u.id=eu.id
LEFT OUTER JOIN context ctx
ON ( u.id = ctx.instanceid
AND ctx.contextlevel =30 )
LEFT OUTER JOIN user_lastaccess ul
ON ( u.id = ul.userid
AND ul.courseid =5 )
WHERE
u.deleted =0
AND u.username != 'guest'
ORDER BY lastaccess DESC
LIMIT 20
Sub-task
Closed
Major
Just committed a simpler fix based on discussion in Moodle HQ chat - remove one DISTINCT!
Good one, ML!