Is there any sql gurus out there who can help refine the below query
[code]SELECT si_invoices.id, (select name from si_biller where si_biller.id = si_invoices.biller_id) as Biller, (select name from si_customers where si_customers.id = si_invoices.customer_id) as Customer, (select sum(si_invoice_items.total) from si_invoice_items WHERE si_invoice_items.invoice_id = si_invoices.id) as INV_TOTAL, ( select IF ( isnull(sum(ac_amount)) , '0', sum(ac_amount)) from si_account_payments where ac_inv_id = si_invoices.id ) as INV_PAID, (select (INV_TOTAL - INV_PAID)) as INV_OWING , date_format(date,'%Y-%m-%e') as Date , (select datediff(now(),date)) as Age, (CASE WHEN datediff(now(),date) <= 14 THEN '0-14' WHEN datediff(now(),date) <= 30 THEN '15-30' WHEN datediff(now(),date) <= 60 THEN '31-60' WHEN datediff(now(),date) <= 90 THEN '61-90' ELSE '90+' END ) as Aging, (select pref_description from si_preferences where pref_id = preference_id) as Type FROM si_invoices,si_account_payments,si_invoice_items, si_biller, si_customers WHERE si_invoice_items.invoice_id = si_invoices.id GROUP BY si_invoices.id [/code]
this query works and give me the desired result BUT takes ages to run
can someone edit this sql to make it run all nice and fast?????
Rick Pearson aka 'the man' aka 'sql freak' from ripegroup.com has stepped up to the sql plate and delivered unto us a killer query
[code]SELECT si_invoices.id, si_biller.name AS Biller, si_customers.name AS Customer, sum(si_invoice_items.total) AS INV_TOTAL, IF ( ISNULL(SUM(ac_amount)) , '0', SUM(ac_amount)) AS INV_PAID, (SUM(si_invoice_items.total) - IF(ISNULL(sum(ac_amount)), '0', SUM(ac_amount))) AS INV_OWING , DATE_FORMAT(date,'%Y-%m-%e') AS Date , (SELECT DateDiff(now(),date)) AS Age, (CASE WHEN DateDiff(now(),date) <= 14 THEN '0-14' WHEN DateDiff(now(),date) <= 30 THEN '15-30' WHEN DateDiff(now(),date) <= 60 THEN '31-60' WHEN DateDiff(now(),date) <= 90 THEN '61-90' ELSE '90+' END) AS Aging, si_preferences.pref_description AS Type FROM si_invoices LEFT JOIN si_account_payments ON ac_inv_id = si_invoices.id LEFT JOIN si_invoice_items ON si_invoice_items.invoice_id = si_invoices.id LEFT JOIN si_biller ON si_biller.id = si_invoices.biller_id LEFT JOIN si_customers ON si_customers.id = si_invoices.customer_id LEFT JOIN si_preferences ON pref_id = preference_id GROUP BY si_invoices.id [/code]
basically my original query took 7 seconds to return 12 records (which is amazingly bad!). This new query takes 0 seconds to return the same result!!!!
Yes Justin, the SELECT DateDiff will run faster as it is run only once and is considered to be a constant in the optimised SQL engines from MySQL 4.1.x onwards I assume. It may not run in the earlier versions of MySQL and the pre-optimised versions of 4.1.x. I was only lookng towards backwards compatibility and the avoidance of a sub-SELECT where one may not be warranted. In this case if the speed differential is significant, then the SELECT DateDiff is the way to go.
