Best Practice to build schema database for my case

Question

I'm working in an application with big database and I'm suffering for slow some of my query.

So , I need to give me some advicess for changing the schema if I need.

I hava an application about Transaction and Invoices, each invoice has more than 100-250 transactions.

So , I have a query to get all invoices and sum the amount of transaction.

this query take more than 10s to run.

Can you give me an advice to improve this query.

this query will get 10k invoices and each invoice has 100-250 transaction

SELECT id, amount, (
  select sum(amount)
  from transactions
  where invoice_id = invoices.id
) as paid from invoices

thanks

Doesn't really sound like there's anything wrong with your structure. Looks like a typical one-to-many relationship — Phil, Sep 16 '18 at 06:36
I thought maybe if I added new column in Invoice Table (Total_Paid) and it will be updated after any new transaction. It will make the query faster — Majeed, Sep 16 '18 at 06:50

Zaynul Abadin Tuhin · Answer 1 · 2018-09-16T06:49:59.623

2

use join and in case if you have no foreign key relationship with transactions table then create that relationship with invoices table

SELECT id,sum(amount)
 from invoices i join transactions t on t.invoice_id = i.id
group by id

edited Sep 16 '18 at 06:49

answered Sep 16 '18 at 06:31

OP should also ensure that `transaction (invoice_id)` is a foreign key to `invoice (id)` – Phil Sep 16 '18 at 06:35
@Phil it seems me op already has that relation though i assume it by seeing his query – Zaynul Abadin Tuhin Sep 16 '18 at 06:46
We don't know for sure so I thought it might be worth pointing out – Phil Sep 16 '18 at 06:46
@ZaynulAbadinTuhin . . . This query will not include invoices that have no transactions. The OPs does include these. – Gordon Linoff Sep 16 '18 at 12:15

score 0 · Answer 2 · answered Sep 16 '18 at 12:14

Your query is fine logically. I would write it as:

select i.id, i.amount,
       (select sum(t.amount)
        from transactions t
        where t.invoice_id = i.id
       ) as paid
from invoices i;

For performance, you want an index on transactions(invoice_id, amount):

create index idx_transaction_invoice_amount on transactions(invoice_id, amount);

2 Answers2