Subset Query Speed Help from .46 second to 7 minutes!
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