we've got an old sql query that powers the 'Manage Customers' page thats slow as a dog to run. if anyone has a few minutes to review and rewrite this query so it run faster it would be greatly appreciated.
theres the sql
SELECT c.id AS CID, c.name AS name, ( SELECT (CASE WHEN c.enabled = 0 THEN 'Disabled' ELSE 'Enabled' END ) ) AS enabled, ( SELECT coalesce(sum(ii.total), 0) AS total FROM si_invoice_items ii INNER JOIN si_invoices iv ON (iv.id = ii.invoice_id) WHERE iv.customer_id = CID ) AS customer_total, ( SELECT coalesce(sum(ap.ac_amount), 0) AS amount FROM si_payment ap INNER JOIN si_invoices iv ON (iv.id = ap.ac_inv_id) WHERE iv.customer_id = CID ) AS paid, ( SELECT customer_total - paid ) AS owing FROM si_customers c WHERE c.domain_id = '1' ORDER BY name ASC;
just post your improve query in the Simple Invoices forum: