mysql - 我想优化这个查询，它需要很多时间来执行

Question

我想优化这个需要大量时间执行的查询。

SELECT DISTINCT
  j1.job_id,
  j1.job_name,
  j1.client_id,
  j1.job_status_id,
  j1.int_job_status_id,
  j1.job_type_id,
  j1.job_due_date,
  j1.job_received,
  c1.client_name,
  c1.id,
  j1.period,
  j1.notes,
  j1.staff_notes,
  j1.job_genre,
  j1.job_submitted,
  j1.mas_Code,
  DATE_FORMAT(j1.job_completed_date, '%d/%m/%Y')    completedDate,
  j1.invoiceno,
  j1.is_active,
  p1.name,
  s1.job_status
FROM CLIENT c1,
  pr_practice p1,
  sub_subactivity sa,
  job_status s1,
  job j1
  LEFT JOIN task t
    ON j1.job_id = t.job_id
  LEFT JOIN task_assign ta
    ON ta.task_id = t.task_id
WHERE j1.client_id = c1.client_id
    AND c1.id = p1.id
    AND t.job_id = j1.job_id
    AND sa.sub_Code = j1.job_type_id
    AND j1.job_status_id = s1.job_status_id
    AND j1.discontinue_date IS NULL
    AND j1.job_submitted = 'Y'
    AND j1.job_status_id <> 7
    AND p1.id = c1.id
    AND (j1.job_type_id IN(8,5,6,9,19,19,7,16,17)
         AND (FIND_IN_SET(134,p1.sr_manager)
               OR FIND_IN_SET(134,p1.manager)
               OR FIND_IN_SET(134,p1.tl1)
               OR FIND_IN_SET(134,p1.tl2)
               OR FIND_IN_SET(134,p1.tl3)
               OR FIND_IN_SET(134,p1.tl4)
               OR FIND_IN_SET(134,p1.sales))
          OR ta.assignee_id IN(134))
    AND j1.is_active = 1
ORDER BY j1.job_received DESC, j1.job_id DESC
LIMIT 0,25

客户表

client_id int(11)  | id int(11) (practice Id)

练习表

id int(11) | sr_manager | manager | t1 |t2 |t3 |t4 |sales varchar(255)

工作表

j1.job_id,   j1.job_name,   j1.client_id,   j1.job_status_id,   j1.int_job_status_id,  j1.job_type_id,   j1.job_due_date,   j1.job_received,  j1.period,
  j1.notes,  j1.staff_notes,  j1.job_genre,  j1.job_submitted,  j1.mas_Code etc.

任务表

task_id | id (practice id) | client_id | job_id 

任务分配者

id | task_id | assignee_id

请多多指教!

Answer 1

试试这个:

SELECT DISTINCT j1.job_id, j1.job_name, j1.client_id, j1.job_status_id, 
         j1.int_job_status_id, j1.job_type_id, j1.job_due_date, j1.job_received, 
         c1.client_name, c1.id, j1.period, j1.notes, j1.staff_notes, j1.job_genre, 
         j1.job_submitted, j1.mas_Code, DATE_FORMAT(j1.job_completed_date, '%d/%m/%Y') completedDate, 
         j1.invoiceno, j1.is_active, p1.name, s1.job_status
FROM CLIENT c1, 
INNER JOIN pr_practice p1 ON c1.id = p1.id 
INNER JOIN job j1 ON j1.client_id = c1.client_id AND j1.is_active = 1
INNER JOIN sub_subactivity sa ON sa.sub_Code = j1.job_type_id
INNER JOIN job_status s1 ON j1.job_status_id = s1.job_status_id
LEFT JOIN task t ON j1.job_id = t.job_id
LEFT JOIN task_assign ta ON ta.task_id = t.task_id
WHERE j1.discontinue_date IS NULL AND j1.job_submitted = 'Y' AND 
        j1.job_status_id <> 7 AND j1.job_type_id IN (8,5,6,9,19,19,7,16,17) AND 
       FIND_IN_SET(134, CONCAT_WS(',', p1.sr_manager, p1.manager, p1.tl1, p1.tl2, p1.tl3, p1.tl4, p1.sales, ta.assignee_id))    
ORDER BY j1.job_received DESC, j1.job_id DESC
LIMIT 0,25