我有 6 个查询,如下所示。
- 每个运行需要 6 秒
- 页面加载总共需要 36 秒
有没有办法优化这些类型的查询?
SELECT
tickets.ticketID,
tickets.ticket,
tickets.name1,
tickets.address1,
tickets.city,
tickets.cstate,
tickets.zip,
tickets.caller_type,
tickets.phone,
tickets.caller,
tickets.caller_phone,
tickets.contact,
tickets.contact_phone,
tickets.call_back,
tickets.location,
tickets.printable_text,
tblnotes.ntDate,
tblnotes.ntText,
tblstatus.stDesc,
tblUsers.username
FROM tblusers
RIGHT OUTER JOIN tickets ON tblusers.ID = tickets.ownerID
LEFT OUTER JOIN tblstatus ON tblstatus.stID = tickets.statusID
LEFT OUTER JOIN tblnotes ON tblnotes.ntID = tickets.noteID
WHERE tblstatus.stDesc <> "Closed"
编辑:试试这个
SELECT
tickets.ticketID,
tickets.ticket,
tickets.name1,
tickets.address1,
tickets.city,
tickets.cstate,
tickets.zip,
tickets.caller_type,
tickets.phone,
tickets.caller,
tickets.caller_phone,
tickets.contact,
tickets.contact_phone,
tickets.call_back,
tickets.location,
tickets.printable_text,
tblnotes.ntDate,
tblnotes.ntText,
tblstatus.stDesc,
tblUsers.username
FROM tickets
INNER JOIN tblusers ON tblusers.ID = tickets.ownerID
INNER JOIN tblstatus ON tblstatus.stID = tickets.statusID
LEFT OUTER JOIN tblnotes ON tblnotes.ntID = tickets.noteID
WHERE tickets.statusID <> 3
发布作为答案,因为我无法发表评论
您有条件where tblstatus.stDesc <> "Closed"
假设您在 stID 上有一个索引将其更改为where tblstatus.stID <> put the id value
还可以将您的左外连接更改为内部连接,因为您有 where 条件的任何方式,您可以将左连接保留在tblnotes
,因为我不确定它是否有对应于tbltickets
的行
我还将tickets
表移动到from
,然后与 tblusers
进行内部联接
仅当联接表可能没有数据,但仍希望显示主表中的数据时,才使用 left outer join