mysql - 查询运行缓慢并带有子查询

该查询正在与子查询一起慢慢进行。我还没有创建临时表的权限，但是仍然可以进一步优化此查询吗？还是我在某个地方使用了损坏的逻辑？

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT
       CONCAT( u.firstname , ' ' , u.lastname) as Student_name,
        u.id as Student_ID,
        FROM_UNIXTIME(u.firstaccess) as first_moodle_access,
        FROM_UNIXTIME(u.lastaccess) as last_moodle_access,
        FROM_UNIXTIME(last.timeaccess) as last_course_access,
        grp.name as Cohorts,
        cour.fullname as course
FROM
mdl_user as u
Inner Join mdl_user_lastaccess as last on u.id = last.userid
Inner Join mdl_course as cour on last.courseid = cour.id
Inner Join mdl_groups as grp on cour.id = grp.courseid
WHERE u.lastlogin = (
                     Select Max(u2.lastlogin)
                     From mdl_user as u2
                     where u2.lastlogin = u.lastlogin
                    )
AND cour.id = '10189' AND
u.firstaccess > '0' AND
u.lastaccess > '0';

最佳答案

在大多数情况下，相关的子查询效率很低，您可以通过交付的表获得更好的性能。
大多数时候，可以使用INNER JOIN重写相关子查询。

我认为可以将您的相关子查询重写如下，但是您需要检查结果是否相同，因为我不知道您的表结构和数据。

SELECT
       CONCAT( u.firstname , ' ' , u.lastname) as Student_name,
        u.id as Student_ID,
        FROM_UNIXTIME(u.firstaccess) as first_moodle_access,
        FROM_UNIXTIME(u.lastaccess) as last_moodle_access,
        FROM_UNIXTIME(last.timeaccess) as last_course_access,
        grp.name as Cohorts,
        cour.fullname as course
FROM
mdl_user as u
Inner Join mdl_user_lastaccess as last on u.id = last.userid
Inner Join mdl_course as cour on last.courseid = cour.id
Inner Join mdl_groups as grp on cour.id = grp.courseid
Inner Join(
  Select
    Max(lastlogin)
  FROM
    mdl_user
) AS u2
ON
  u.lastlogin = u2.lastlogin
WHERE
cour.id = '10189' AND
u.firstaccess > '0' AND
u.lastaccess > '0';

关于mysql - 查询运行缓慢并带有子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41552178/