mysql - 极慢的查询

Question

下面是一个需要 30 多秒才能运行的查询。根据我运行的类似查询，我看不出问题在哪里。我唯一的想法是将工作用户 ID 加入到 job_applicants 用户 ID，但它们需要映射。

SELECT DISTINCT u.user_id, u.first_name, u.last_name FROM users u
LEFT OUTER JOIN employee_access ea ON ea.user_id = u.user_id
LEFT OUTER JOIN confirmation c ON c.user_id = u.user_id
LEFT OUTER JOIN job_applicants a ON a.user_id = u.user_id
LEFT OUTER JOIN job j ON j.job_id = a.job_id
WHERE ea.access_id = 4 OR c.access_id = 4 OR (a.process_level = 0 AND j.access_id = 4) 
ORDER BY u.last_name asc

Answer 1

使用exists:

select u.*
from users u
where exists (select 1
              from employee_access ea 
              where ea.user_id = u.user_id and ea.access_id = 4
             ) or
      exists (select 1
              from confirmation c 
              where c.user_id = u.user_id and c.access_id = 4
             ) or
      exists (select 1
              from job_applicants a join
                   job j 
                   on j.job_id = a.job_id
              where a.user_id = u.user_id and
                    a.process_level = 0 AND j.access_id = 4
             )
order by u.last_name;

这将阻止所有笛卡尔积和最终删除重复项。

我会推荐索引:

• 用户(last_name, user_id)
• employee_access(user_id, access_id)
• 确认(user_id, access_id)
• job_applicants(user_id, process_level, job_id)
• 工作(job_id，access_id)