CODEWITHSUNDEEP
mysqlsql
webmaster Chp
webmaster Chp

Reputation: 97

Request take too many times

I would like to know which request (and which index) will be the best to do that. I Use Symfony. But for this request I used normal SQL. I did tables with doctrine.

I would like to have the asset of each Client.

I have around

Tables:

Client ~ 15 000 rows ->  Index : foreign key,id, reference, 
Invoice ~ 750 000 rows -> Index : foreign key,id, reference, 
Invoice_line ~ 4 000 000 rows -> Index : foreign key, qty, price, reference, 
"credit_note" ~ 40 000 rows -> Index : foreign key,id, reference, 
"credit_note_line" ~ 61 000 rows -> Index : foreign key, id, qty, price, reference, 
Product ~ 75 000 rows -> Index : foreign key, id, reference,

the line represent one product with is price and quantity

Asset for one Client = Total Client's Invoices - Total Client's Credit Notes

So I did this request:

SELECT
    a1.reference,
    a1.id,
    SUM(a1.balance) AS "asset"
FROM
(
    SELECT
        c2.reference,
        c2.id,
        SUM(il.quantity * il.unit_price) AS "balance"
    FROM CLIENT AS
        c2
    INNER JOIN invoice AS i
    ON
        i.client_id = c2.id
    INNER JOIN invoice_line AS il
    ON
        i.id = il.invoice_id
GROUP BY
    c2.reference
UNION ALL
SELECT
    c1.reference,
    c1.id,
    - SUM(cnl.quantity * cnl.unit_price) AS "balance"
FROM CLIENT AS
    c1
INNER JOIN credit_note AS cn
ON
    cn.client_id = c1.id
INNER JOIN credit_note_line AS cnl
ON
    cn.id = cnl.credit_note_id
GROUP BY
    c1.reference
) AS a1
GROUP BY
    reference
ORDER BY
    `asset`
DESC

It give me the right answer but it's very long. How can I reduce the request time? I would like also the asset for N-1 so I have to do this request twice and it take too much time.

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your first query can be simplified to:

SELECT c.reference, MIN(c.id) as id,
       SUM(il.quantity * il.unit_price) AS asset
FROM CLIENT c JOIN
     invoice i
     ON i.client_id = c.id
     invoice_line il
     ON i.id = il.invoice_id
GROUP BY c.reference;

The only way to speed this query is with indexes on the JOIN columns. I would recommend covering indexes:

  • invoice_line(invoice_id, quantity, unit_price)
  • invoice(client_id, id)
  • client(reference, id)

Upvotes: 1

Related Questions