SQL Slow nested query

I have a table called Staff and a table called Supervisors.

Staff has StaffID, FirstName, LastName, etc...

Supervisors contains RelationshipID, StaffID, SupervisorID, SetBy, SetOn, Status.

Basically, the Supervisors tables gives us an audit trail for the Staff self-relationship. We have a table of Staff, and we have a table of Staff:Staff relationships (supervisor:staff) with some extra information (obsolete, current, incorrect) and a StaffID who set it and when they set it.

Now, I'm writing a query to find all orphaned staff members. I have:

SELECT *
  FROM Staff
 WHERE StaffID NOT IN (SELECT StaffID
                         FROM Supervisors
                        WHERE Status = 0 
                           OR Status = 2);

(status 0 is initial load from corporate DB and 2 is modified record which has been verified. All others are 'obso开发者_运维技巧lete', 'incorrect', etc...)

The issue is I have over 6000 staff and over 5000 staff:supervisor relationships and this is basically an NxM query meaning MySQL has to sift through 3 million permutations.

I'm not an SQL ninja, is there a better way to do this?

(Note, I do not expect to be running this particular query very often at all)

Assuming SUPERVISOR.staffid and SUPERVISOR.status columns are not nullable, use:

   SELECT st.*
     FROM STAFF st
LEFT JOIN SUPERVISOR s ON s.staffid = st.staffid
                      AND s.status NOT IN (0,2)
    WHERE s.staffid IS NULL

Otherwise, NOT IN/NOT EXISTS are equivalent & perform better if the columns are nullable.

For more info:

• http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
• http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/

This would be better performed as a join rather than a NOT IN:

SELECT st.* 
FROM Staff st
LEFT JOIN Supervisors su ON st.StaffID = su.StaffID 
          AND (su.Status <> 0 AND su.Status <> 2)
WHERE su.StaffId IS NULL

Here's how I transformed it:

NOT IN (SELECT StaffID FROM Supervisors WHERE Status = 0 OR Status = 2)

by applying Boole's law is equivalent to

IN (SELECT StaffID FROM Supervisors WHERE Status <> 0 AND Status <> 2);

(assuming Status can never be NULL) and from there is just a join.