SQL Query Optimization with NOT IN statement

Please help me optimize this query:

$result =开发者_如何学C mysql_query('
SELECT DISTINCT caseNumber 
FROM master_break 
WHERE invoiceNumber='$invoice' 
AND bay='$bay' AND  caseNumber 
NOT IN( SELECT DISTINCT caseNumber 
        FROM shipped_data 
        WHERE status='' AND bay='$bay') 
        ORDER BY caseNumber ASC");

Thanks.

DISTINCT means that for each record added to the result set, MySQL has to check the entire set for its existence before adding the record. This means that it has a O(n^2) to insert. When using it in an IN clause, this can mean that you're adding substantially more work for zero benefit. Try this instead:

SELECT DISTINCT caseNumber 
FROM master_break 
WHERE invoiceNumber='$invoice' 
AND bay='$bay' 
AND caseNumber NOT IN( 
     SELECT caseNumber 
     FROM shipped_data 
     WHERE status='' AND bay='$bay') 
ORDER BY caseNumber ASC

Obviously index caseNumber on shipped_data, and bay on both.

SELECT DISTINCT caseNumber
FROM master_break
LEFT JOIN shipped_data ON master_break.caseNumber = shipped_data.caseNumber
WHERE invoiceNumber='$invoice'
  AND master_break.bay='$bay'
  AND shipped_data.status !=''
  AND shipped_data.bay !='$bay'
ORDER BY caseNumber ASC