我正在使用下面的mysql查询来获取一些所需的结果,但问题是它需要更多的时间。目前这个查询的执行时间是7456毫秒,这对我的项目来说是不可接受的,我想优化这个查询,有什么想法吗?。
SELECT *, DATEDIFF(NOW(),ticketstatus_tbl.updation_date) AS problem_age,images_tbl.image_path
FROM ticketstatus_tbl
LEFT JOIN question_tbl ON ticketstatus_tbl.question_id = question_tbl.question_id
LEFT JOIN ticketing_tbl ON ticketstatus_tbl.related_ticket_id = ticketing_tbl.ticket_id
LEFT JOIN department_tbl ON question_tbl.question_dept = department_tbl.department_id
LEFT JOIN branch_tbl ON ticketstatus_tbl.branch_id = branch_tbl.id
LEFT JOIN images_tbl ON images_tbl.question_id = ticketstatus_tbl.question_id and images_tbl.branch_id = ticketstatus_tbl.branch_id
WHERE (ticketstatus_tbl.ticket_status NOT IN ('Close')
AND question_tbl.is_active_question = 1
AND ticketstatus_tbl.display_status = '1'
AND ticketstatus_tbl.flag_color = 'Yellow'
AND department_tbl.department_name = 'Admin')order by ticket_number ASC LIMIT 0 ,5
感谢
首先,您不需要所有的left outer join
,因为您的where
子句将撤消其中的大部分。我的猜测是,所有这些都可以变成内部连接,但至少:
SELECT *, DATEDIFF(NOW(),ticketstatus_tbl.updation_date) AS problem_age,images_tbl.image_path
FROM ticketstatus_tbl
JOIN question_tbl ON ticketstatus_tbl.question_id = question_tbl.question_id
LEFT JOIN ticketing_tbl ON ticketstatus_tbl.related_ticket_id = ticketing_tbl.ticket_id
JOIN department_tbl ON question_tbl.question_dept = department_tbl.department_id
LEFT JOIN branch_tbl ON ticketstatus_tbl.branch_id = branch_tbl.id
LEFT JOIN images_tbl ON images_tbl.question_id = ticketstatus_tbl.question_id and images_tbl.branch_id = ticketstatus_tbl.branch_id
WHERE ticketstatus_tbl.ticket_status NOT IN ('Close')
AND question_tbl.is_active_question = 1
AND ticketstatus_tbl.display_status = '1'
AND ticketstatus_tbl.flag_color = 'Yellow'
AND department_tbl.department_name = 'Admin'
order by ticket_number ASC LIMIT 0 ,5;
其次,您正在对ticketstatus_tbl
进行筛选。您应该在ticketstatus_tbl(display_status, flag_color, ticket_status, question_id)
上有一个复合索引。如果可以,请将ticket_status not in ('Close')
更改为肯定语句:ticket_status in (
打开, 'In Progress', . . .)
。这样可以更容易地使用索引。
这是一个开始。