SQL“或”在 where 子句中导致不使用索引
我有一个查询如下：
SELECT COUNT(Table1.Identifier) AS NonCancelCnt
FROM Table1, Table2
LEFT JOIN eventattendees ON eventattendees.AttendeeID = 47322
LEFT JOIN eventsignup ON eventattendees.AttendeeID = eventsignup.AttendeeID
LEFT JOIN transactions on transactions.registrationID=eventsignup.regid
WHERE ((eventsignup.EventID = Table1.Identifier) Or (eventsignup.EventID = Table1.AttendanceLinkID))
“OR”子句导致不使用索引。如果删除任一部分，我的执行路径就会从 95,000 变为 200，并且速度会大幅提高。
我在返工这样的事情上不是很有经验，这样做的最佳选择是什么？
I've got a query as follows:
SELECT COUNT(Table1.Identifier) AS NonCancelCnt
FROM Table1, Table2
LEFT JOIN eventattendees ON eventattendees.AttendeeID = 47322
LEFT JOIN eventsignup ON eventattendees.AttendeeID = eventsignup.AttendeeID
LEFT JOIN transactions on transactions.registrationID=eventsignup.regid
WHERE ((eventsignup.EventID = Table1.Identifier) Or (eventsignup.EventID = Table1.AttendanceLinkID))
The "OR" clause is causing no index to be used. If I remove either portion, my execution path goes from 95,000 to 200, and speed is drastically increased.
I'm not very experienced in reworking such a thing, what is my best option for doing so?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
首先，您应该重写查询以指定 Table1、Table2 和 eventattendees 的联接方式。还可以选择是否要在 WHERE 子句中或在 JOIN 关键字之后指定用于联接的列。稍微清理一下之后，优化器可能会更好地选择要使用的正确索引。
如果仍然不起作用，您可以使用 SQL 提示来指定您希望优化器使用的索引：
WITH INDEX(IX_nameofindex)
First, you should rewrite your query to specify how Table1, Table2 and eventattendees are joined. Also choose whether you want to specify the columns to use to join in the WHERE clause or after the JOIN keyword. After you clean it up a bit, the optimizer may do a better job of picking the proper index to use.
If that still doesn't work, you can use a SQL hint to specify the index you want the optimizer to use:
WITH INDEX(IX_nameofindex)
不理解 Table1 和 Table2 是什么，也没有以任何形状连接，您将得到一个笛卡尔结果（对于 Table1 中的每条记录，将与 Table2 中的每条记录连接）
此外，您的 where 子句可以用 IN 简化条款
Not understanding what Table1 and Table2 are, nor are they joined in any shape, you will get a Cartesian result (for each record in Table1, will be joined with each record in Table2)
Additionally, your where clause could just be simplified with an IN clause