Best Practice to build schema database for my case

1907 views mysql
8

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

answered question

Doesn't really sound like there's anything wrong with your structure. Looks like a typical one-to-many relationship

Share transactions table structure

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

2 Answers

9

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

posted this
8

If you take it as proc ,you will get better result

create proc CalculateTransaction 
)
as
    SELECT id,sum(amount)
     from invoices i leftjoin transactions t on t.invoice_id = i.id
    group by id

then run your proc:

exec CalculateTransaction 

posted this

Have an answer?

JD

Please login first before posting an answer.