MySQL Forums
Forum List  »  Optimizer & Parser

How could this SQL be optimized?
Posted by: feng guo
Date: May 29, 2014 11:26PM

This SQL I think should be optimized as consideration of collection,but I still couldn't find it out:
--------------------------------------------------------------------------
SELECT ID,WAYBILL_NO,EXP_TYPE,PKG_QTY,EXPRESS_CONTENT_CODE,EFFECTIVE_TYPE_CODE
FROM T_EXP_OP
WHERE
ORDERID NOT IN(SELECT ORDERID FROM T_EXP_OP WHERE AUX_OP_CODE IN ('NEW','UPDATE','DELETE') AND ((OP_CODE IN (176, 162, 171, 131, 136) AND EXP_TYPE IN ('10', '20', '30')) OR (OP_CODE IN (191, 121) AND EXP_TYPE IN ('10', '20')) OR (OP_CODE IN (181, 111)AND EXP_TYPE = '10'))) ;
--------------------------------------------------------------------------
Someone gives me this optimized result:
--------------------------------------------------------------------------
SELECT ID, WAYBILL_NO, EXP_TYPE, PKG_QTY,EXPRESS_CONTENT_CODE, EFFECTIVE_TYPE_CODE
FROM T_EXP_OP
WHERE
AUX_OP_CODENOT IN (‘NEW’, ‘UPDATE’, ‘DELETE’)

AND (OP_CODE NOT IN (176, 162, 171, 131, 136, 191, 121, 181,111)

AND EXP_TYPE NOT IN(‘10’, ‘20’, ‘30’))
--------------------------------------------------------------------------

Options: ReplyQuote


Subject
Views
Written By
Posted
How could this SQL be optimized?
2446
May 29, 2014 11:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.