Skip navigation links

MySQL Forums :: Performance :: finding the better way to calculate po balance !


Advanced Search

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
finding the better way to calculate po balance ! 1195 mo melvin 06/16/2011 04:39AM
Re: finding the better way to calculate po balance ! 548 Rick James 06/17/2011 09:57AM
Re: finding the better way to calculate po balance ! 637 mo melvin 06/17/2011 11:00AM
Re: finding the better way to calculate po balance ! 554 Rick James 06/17/2011 10:41PM
Re: finding the better way to calculate po balance ! 601 mo melvin 06/18/2011 08:56AM
Re: finding the better way to calculate po balance ! 425 Rick James 06/18/2011 09:50AM
Re: finding the better way to calculate po balance ! 518 mo melvin 06/18/2011 10:36AM
Re: finding the better way to calculate po balance ! 530 Rick James 06/18/2011 03:02PM


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.