How can I optimize this query?

Question

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
                 )

Answer 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

Answer 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.

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.