MySQL Forums
Forum List  »  Newbie

Subset Query Speed Help from .46 second to 7 minutes!
Posted by: Brian Pilati
Date: August 10, 2005 03:11PM

Hello, I need to combine two queries into one query. Here are the basics.

select t3.id , t4.id , (select * from t1 , t2 where t1.id = t3.id and t2.id = t4.id) , [more variables] from t3 , t4 where [lots of stuff].

If I run query 1 and query 2 alone the response times are .13 and .43 seconds, respectively. However, combined into one query the response time is a whopping 7 plus minutes. Any ideas on why this is happening. I have included the two queries.

Query 1:

select gl2.debit from general_ledger gl2 , invoices i2 where ( ( i2.posted = 'true' and gl2.general_ledger_id = 222 and gl2.type = 'Invoiced Transfer' ) or ( i2.posted = 'false' and gl2.general_ledger_id = 131 and gl2.type ='Prepayment' ) ) and gl2.parent_id = i2.id and i2.date_posted <= '2003-01-31 23:59:59';

Query 2:

select distinct i.id , gl.id , i.reference_number , if( gl.credit > 0 , gl.credit , - gl.debit ) as amount , round( ( unix_timestamp( '2005-08-01 23:59:59' ) - unix_timestamp( i.date_posted ) ) / 86400 ) as elapsed , gl.date from location l left join company c on l.company_id = c.id left join countries co on l.country = co.id , location_connection lc , invoices i , general_ledger gl WHERE l.accounting_type = 'Vendor' and lc.location_id = '37' and lc.invoice_id = i.id and i.posted = 'true' and gl.posted = 'true' and gl.parent_id = i.id and ( gl.type ='Invoice Net' || gl.type = 'Prepayment' ) and ( lc.type = 'Invoice To' || lc.type = 'Bank Transfer' ) and gl.gl = '5' and i.date_posted <= '2003-01-31 23:59:59' ORDER BY c.name , l.city , gl.date limit 11;

Query 3:

select distinct i.id , gl.id , i.reference_number , if( gl.credit > 0 , gl.credit , - gl.debit ) as amount , ( select gl2.debit from general_ledger gl2 , invoices i2 where ( ( i2.posted = 'true' and gl2.general_ledger_id = gl.id and gl2.type = 'Invoiced Transfer' ) or ( i2.posted = 'false' and gl2.general_ledger_id = i.id and gl2.type ='Prepayment' ) ) and gl2.parent_id = i2.id and i2.date_posted <= '2003-01-31 23:59:59' ) as payment , round( ( unix_timestamp( '2005-08-01 23:59:59' ) - unix_timestamp( i.date_posted ) ) / 86400 ) as elapsed , gl.date from location l left join company c on l.company_id = c.id left join countries co on l.country = co.id , location_connection lc , invoices i , general_ledger gl WHERE l.accounting_type = 'Vendor' and lc.location_id = '37' and lc.invoice_id = i.id and i.posted = 'true' and gl.posted = 'true' and gl.parent_id = i.id and ( gl.type ='Invoice Net' || gl.type = 'Prepayment' ) and ( lc.type = 'Invoice To' || lc.type = 'Bank Transfer' ) and gl.gl = '5' and i.date_posted <= '2003-01-31 23:59:59' ORDER BY c.name , l.city , gl.date limit 11;

Thanks.
Brian

Options: ReplyQuote


Subject
Written By
Posted
Subset Query Speed Help from .46 second to 7 minutes!
August 10, 2005 03:11PM


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.