User authentication is temporarily unavailable. If this situation persists, please inform your Wiki Admin.

Can anyone make this sql query faster

Hi Guys,

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:
- http://simpleinvoices.org/forum/discussion/864/can-anyone-make-this-sql-query-faster/

Cheers

Justin

Discussion

Artem Russakovskii, 2009/09/16 16:20

Can you provide a dump of test data where this query would actually be visibly slow?

The main problem is you have no indexes, AT ALL, on your tables. Without them, you're doomed. I am making a list now that would speed up this query but I want to test it with a lot of data if you give it to me, before posting back here.

Artem Russakovskii, 2009/09/16 16:47

Anyway, I posted my solution in the forum.

Artem http://beerpla.net http://twitter.com/ArtemR

justin, 2009/09/16 23:17

thanks Artem!

cheers

justin

 
 | blog/can_anyone_make_this_sql_query_faster.txt · Last modified: 2016/10/11 06:14 (external edit)