>我的查询有很大的问题，执行时间超过 10 秒
许多子查询和左联接
这是我的查询
SELECT tickets.tic_id,
tickets.tic_title,
tic_statue,
tic_priority,
tickets.tic_msg,
customers.cus_name,
employee.emp_name,
count(comments.com_id),
customers.cus_id ,
(
SELECT comments.com_msg
from comments
WHERE tickets.tic_id = comments.tic_id
order by com_id DESC
limit 1
) AS last_comment,
(
SELECT customers.cus_name
from customers INNER JOIN comments on (customers.cus_id = comments.tic_uid)
where comments.com_msg = last_comment
LIMIT 1
)AS by_customer,
(
SELECT employee.emp_name
from employee INNER JOIN comments on (employee.emp_id = comments.tic_emp)
where comments.com_msg = last_comment
LIMIT 1)AS by_employee,
tic_date,
(SELECT comments.com_statue
from comments
WHERE tickets.tic_id = comments.tic_id
order by com_id DESC
limit 1)
AS last_comment_color
FROM tickets
LEFT JOIN employee
on (tickets.tic_emp = employee.emp_id)
LEFT JOIN customers
on (tickets.tic_cus = customers.cus_id)
LEFT join cats
on (tickets.tic_cat = cats.cat_id)
LEFT JOIN comments
on (tickets.tic_id = comments.tic_id)
GROUP by tickets.tic_id
ORDER BY tickets.tic_statue ASC, comments.com_time DESC
limit 50
结果工作正常，但时间很长
索引 ：tickets.tic_idcomments.com_idcomments.tic_idcustomers.cus_id
谢谢:)
试试这个
SELECT tickets.tic_id,
tickets.tic_title,
tic_statue,
tic_priority,
tickets.tic_msg,
customers.cus_name,
employee.emp_name,
count(comments.com_id),
customers.cus_id ,
@cm := coalesce( last_comment.COM_MSG, 000000 ) as COM_MSG,
@cn := coalesce( by_customer.CUS_NAME, 000000 ) as CUS_NAME,
@be := coalesce( by_employee.EMP, 000000 ) as EMP,
@cs := coalesce( last_comment_color.COM_STATUE, 000000 ) as COM_STATUE
FROM
( select
@cm := 0,
@cn := 0,
@be := 0,
@cs := 0
) sqlvars,
tickets
LEFT JOIN (
SELECT com_msg AS COM_MSG
from comments
order by com_id DESC
limit 1
) AS last_comment
ON tickets.tic_id = last_comment.tic_id
LEFT JOIN (
SELECT com_msg,cus_name AS CUS_NAME
from customers INNER JOIN comments on (cus_id = comments.tic_uid)
LIMIT 1
)AS by_customer,
ON by_customer.com_msg = last_comment
LEFT JOIN (
SELECT com_msg,emp_name AS EMP
from employee INNER JOIN comments on (emp_id = comments.tic_emp)
LIMIT 1)AS by_employee
ON by_employee.com_msg = last_comment
LEFT JOIN (SELECT tic_id ,com_statue AS COM_STATUE
from comments
order by com_id DESC
limit 1)
AS last_comment_color
ON tickets.tic_id = last_comment_color.tic_id
LEFT JOIN employee
on (tickets.tic_emp = employee.emp_id)
LEFT JOIN customers
on (tickets.tic_cus = customers.cus_id)
LEFT join cats
on (tickets.tic_cat = cats.cat_id)
LEFT JOIN comments
on (tickets.tic_id = comments.tic_id)
GROUP by tickets.tic_id
ORDER BY tickets.tic_statue ASC, comments.com_time DESC
limit 50
我之前用类似的表格创建了一个大型工单系统。 我发现使用last_comment_id列更新主tickets表要容易十倍，这样您就可以在没有子选择的情况下获取所有这些信息。 目前，您必须扫描comments表五次，因此根据其大小，这将需要一段时间。