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