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

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....