gpt4 book ai didi

mysql - 复合索引顺序 MySQL 查询

转载 作者:行者123 更新时间:2023-11-29 18:44:55 25 4
gpt4 key购买 nike

我正在尝试优化以下 mysql 查询。运行时间约为 2.5 秒。我已经阅读了复合索引,但我希望有人可以帮助我理解如何使用包含多个联接、许多 where 条件(包括日期范围)、分组依据和顺序依据的查询对复合索引进行排序计算值。我是否缺少有用的复合索引?有没有更有效的方法可以从这些表中提取数据?非常感谢任何帮助!

SELECT 
branch.name AS branch,
SUM(appointment.status = 'completed') AS `Completed`,
SUM(appointment.status = 'cancelled') AS `Cancelled`,
SUM(appointment.status = 'not completed') AS `Not Completed`,
SUM(appointment.status != 'rescheduled') AS `Total`
FROM rep
JOIN customer ON rep.id = customer.rep_id
JOIN office ON rep.office_id = office.id
JOIN appointment ON customer.id = appointment.customer_id
JOIN branch ON office.branch_id = branch.id
WHERE rep.active= 1
AND rep.group IN (1,2,3)
AND rep.deleted = 0
AND customer.saved = 0
AND (customer.rep_id != appointment.closed_by OR appointment.closed_by IS NULL)
AND customer.rep_id != 0
AND customer.deleted = 0
AND office.visible = 1
AND office.deleted = 0
AND appointment.date >= '2016-12-01'
AND appointment.date < '2017-11-30'
AND appointment.current = 1
GROUP BY branch.id
ORDER BY Completed

这是解释输出:

id: 1
select_type: simple
table: office
type: ref
possible_keys: PRIMARY, deleted_branchID_name, deleted_visible
key: deleted_visible
key_len: 5
ref: const,const
rows: 73
Extra: Using index condition; Using temporary; Using filesort

id: 1
select_type: simple
table: branch
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: office.branch_id
rows: 1
Extra: NULL

id: 1
select_type: simple
table: rep
type: ref
possible_keys: PRIMARY, group_id, office_id, active_deleted
key: office_id
key_len: 5
ref: office.id
rows: 57
Extra: Using index condition; Using where

id: 1
select_type: simple
table: customer
type: ref
possible_keys: PRIMARY, rep_id
key: rep_id
key_len: 4
ref: rep.id
rows: 61
Extra: Using where

id: 1
select_type: simple
table: appointment
type: ref
possible_keys: date, customer_id, closedByID_date, isCurrent_date
key: customer_id
key_len: 4
ref: customer.id
rows: 1
Extra: Using where

最佳答案

一个建议

删除条件周围无用的 ()

  LEFT JOIN customer ON rep.id = customer.rep_id
LEFT JOIN office ON rep.office_id = office.id
LEFT JOIN appointment ON customer.id = appointment.customer_id
LEFT JOIN branch ON office.branch_id = branch.id

关于mysql - 复合索引顺序 MySQL 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44713123/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com