如何优化此查询？

How can I optimize this query? It seems there should be a much easier way to do this. The goal is that it will still be able to be readily turned into a delete statement.

如何优化此查询?似乎应该有一个更容易的方法来做到这一点。目标是它仍然可以很容易地变成删除声明。

SELECT * FROM team 
WHERE team_id IN (
                  SELECT team_id 
                  FROM (
                        SELECT team.team_id, (
                                              SELECT COUNT(*) 
                                              FROM signup 
                                              WHERE signup.team_id = team.team_id
                                             ) AS members 
                        FROM team, schedule, event 
                        WHERE team.schedule_id = schedule.schedule_id 
                        AND schedule.event_id = event.event_id
                        AND event.event_id =183) AS t 
                  WHERE members = 0
                 )

2 个解决方案

#1

A quick glance at this query gives me this:

快速浏览一下这个查询就可以了:

select
    t.*
from
    team t
    inner join schedule s on t.schedule_id = s.schedule_id
    inner join event e on s.event_id = e.event_id
    left outer join signup sp on t.team_id = sp.team_id
where
    e.event_id = 183
    and sp.team_id is null

It looked like you're trying to find all teams that are in an event but are not in the signup table. Is this accurate?

看起来您正在尝试查找所有参加活动但不在注册表中的团队。这准确吗?

Also, I wanted to note that it will be faster to do joins then a bunch of subqueries, especially if the subqueries depend on each row (in your case, they do).

另外,我想要注意,连接和一堆子查询会更快,特别是如果子查询依赖于每一行(在你的情况下,他们这样做)。

Cheers,
Eric

#2

Commenting on Eric's query in this thread, I would recommend (from my mssqlserver background) to use an existential check instead. Doing so (1) makes your intent more clear and (2) gives the optimizer the opportunity to stop examining for a matching team_id after the first one is found (since >=1 matching row causes the existential check to be false). E.g.

在这个帖子中评论Eric的查询时,我建议(从我的mssqlserver背景)改为使用存在性检查。这样做(1)使您的意图更加清晰,并且(2)使优化器有机会在找到第一个之后停止检查匹配的team_id(因为> = 1匹配行导致存在性检查为假)。例如。

select t.* from team t inner join schedule s on t.schedule_id = s.schedule_id inner join event e on s.event_id = e.event_id where e.event_id = 183 and not exists (select * from signup where team_id = t.team_id)

选择t。*来自团队t内部联接计划s on t.schedule_id = s.schedule_id内部联接事件e on s.event_id = e.event_id,其中e.event_id = 183且不存在(select * from signup,其中team_id = t。 TEAM_ID)

But maybe there are mysql specific perf concerns.

但也许有mysql特定的perf问题。

#1