MySQL Forums
Forum List  »  Performance

Re: finding the better way to calculate po balance !
Posted by: mo melvin
Date: June 17, 2011 11:00AM

Hello Rick,

Using HAVING takes 1.83sec, my query (query1://union) takes 2.18sec. It seems no hlep...
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 421181 Using where; Using temporary; Using filesort
2 DERIVED po ALL NULL NULL NULL NULL 210772 Using where
3 UNION invoicedetail ALL NULL NULL NULL NULL 231960 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

For the second suggested query, its takes over 20mins(i terminated now)...
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 203481
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 205486 Using where; Using join buffer
3 DERIVED invoicedetail ALL NULL NULL NULL NULL 231960 Using where; Using temporary; Using filesort
2 DERIVED po ALL NULL NULL NULL NULL 210772 Using where; Using temporary; Using filesort

For your question "Do you really need 500K rows output? "
I need the rows with poqty>outqty only (actually the result should be 3-5K rows per search)

Rick, please....

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: finding the better way to calculate po balance !
837
June 17, 2011 11:00AM


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.