Reputation: 7671
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
)
Upvotes: 0
Views: 296
Reputation: 1912
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.
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)
But maybe there are mysql specific perf concerns.
Upvotes: 0
Reputation: 95203
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
Upvotes: 3