MySQL中的慢子查询 [英] Slow subquery in MySQL

问题描述

我正在尝试使用 CodeIgniter 和 Datatables.net 生成报告.

im trying to generate a report using CodeIgniter and Datatables.net .

现在我正在尝试关闭工作的数量(它是一个人力资源系统).我过去常常查询所有作业，并在 PHP 中执行 foreach，然后进行计算.

Now i'm trying to the amount of closed jobs (its a human resources system). I used to query all jobs and in PHP do a foreach and then doing the calcs.

因为我想使用 Datatables 的所有功能(具体排序)，所以我试图在 mySQL 中进行所有计算.

Because im want to use all the features of Datatables (sorting specifically) im trying to do all the calcs in mySQL.

问题是:第二个子查询非常非常非常慢.

The problem is: the second subquery is very very very slow.

SELECT 
jobs.jobs_id, clients.nome_fantasia, concat_ws(' ', user_profiles.first_name, user_profiles.last_name) as fullname, 
jobs.titulo_vaga, jobs.qtd_vagas, company.name as nome_company, jobs_status.name as      status_name, DATEDIFF(NOW(), jobs.data_abertura) as date_idade, 
(select count(job_cv.jobs_id) from job_cv where job_cv.jobs_id = jobs.jobs_id) as qtd_int,
(select count(distinct job_cv.user_id) from job_cv_history join job_cv on job_cv.job_cv_id = job_cv_history.job_cv_id where job_cv_history.status = '11' and job_cv.jobs_id = jobs.jobs_id ) as fechadas 
FROM (jobs)
JOIN clients ON lients.clients_id=jobs.clients_idJOIN user_profiles ON jobs.consultor_id=user_profiles.user_id
JOIN jobs_status ON jobs.status=jobs_status.jobs_status_id 
JOIN company ON jobs.company_id=company.company_id 
LIMIT 50

有人可以帮助我吗?如果需要，我可以提供更多信息.

Some one can help me? I can provide more information if its needed.

更新

使用 JOIN 而不是 SELECT 的想法适用于第一个子查询，但第二个不是，有没有办法传递变量"以在子查询中使用?喜欢当前的jobs_id?

The idea to use JOIN instead SELECT work with the first subquery but with the second one not, there a way to pass a 'variable' to use inside the subquery? Like the current jobs_id?

再次更新

这条线本身就可以正常工作.但是在子查询中需要大约一分钟的时间来使用磨损值

This line works fine by itself. But inside the subquery take about a minute with worng values

SELECT job_cv.jobs_id,count(distinct job_cv.user_id) AS fechadas
FROM job_cv_history 
JOIN job_cv 
ON job_cv.job_cv_id = job_cv_history.job_cv_id 
WHERE job_cv_history.status = '11'
GROUP BY job_cv.jobs_id

推荐答案

慢不是子查询.事实上，您正在为从外部查询返回的每一行执行这些子查询.将这些改为加入，您应该会观察到性能的提高.

It is not subquery that is slow. It's the fact, that you're executing these subqueries for each row returned from outer query. Move these to joins instead, and you should observe increase in performance.

SELECT 
  jobs.jobs_id, clients.nome_fantasia, concat_ws(' ', user_profiles.first_name, user_profiles.last_name) as fullname, 
jobs.titulo_vaga, jobs.qtd_vagas, company.name as nome_company, jobs_status.name as      status_name, DATEDIFF(NOW(), jobs.data_abertura) as date_idade, 
qtd.qtd_int,
fechadas.fechadas 
FROM (jobs)
JOIN clients ON lients.clients_id=jobs.clients_idJOIN user_profiles ON jobs.consultor_id=user_profiles.user_id
JOIN jobs_status ON jobs.status=jobs_status.jobs_status_id 
JOIN company ON jobs.company_id=company.company_id 
JOIN (
  SELECT jobs_id, count(jobs_id) AS qtd_int FROM job_cv GROUP BY jobs_id
) AS qtd ON qtd.jobs_id = jobs.jobs_id
JOIN (
  SELECT job_cv.user_id, count(distinct job_cv.user_id) AS fechadas
  FROM job_cv_history 
  JOIN job_cv 
  ON job_cv.job_cv_id = job_cv_history.job_cv_id 
  WHERE job_cv_history.status = '11'
  GROUP BY job_cv.user_id
) AS fechadas ON job_cv.jobs_id = jobs.jobs_id
LIMIT 50

这篇关于MySQL中的慢子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！