title: Improving the query speed on orphan record search
body: <p>I have three large tables (plus 1 million rows) </p>
  
  <ul>
  <li>Big1 has a Bigger1ID </li>
  <li>Big2 has a Bigger1ID </li>
  <li>Big3 has a Bigger1ID </li>
  </ul>
  
  <p>That hold an ID to another table (plus 10 million rows) </p>
  
  <ul>
  <li>Bigger1 has a Biggest1ID</li>
  </ul>
  
  <p>Which holds an ID to a final very large table (50 million rows)</p>
  
  <ul>
  <li>Biggest1</li>
  </ul>
  
  <p>The issue we have found is we are carrying orphans in the Biggest1 table that hold a record in the Bigger1 table but not in the Big tables. 
  I created the following query but the performance is not great, this might just be due to the size but I am wondering if there is a faster way to do this. 
  I considered using LEFT JOINS to insert into a temp table, but I did not see any gain. </p>
  
  <pre><code>SELECT 
    Biggest.ID,
    Bigger.ID
  FROM 
      Biggest JOIN Bigger ON Biggest.BiggerID = Bigger.ID
  WHERE 
      Biggest.MyType IN ('type1', ' type2', 'type3')
      AND NOT EXISTS (SELECT * FROM Big1 WHERE Bigger1.BiggerID = Big1.Bigger1ID)
      AND NOT EXISTS (SELECT * FROM Big2 WHERE Bigger1.BiggerID = Big2.Bigger1ID)
      AND NOT EXISTS (SELECT * FROM Big3 WHERE Bigger1.BiggerID = Big3.Bigger1ID)
  </code></pre>
  
  <p>Is there a faster way to do this?  </p>