Reputation: 9
we have the following table structure:
Accounts: id | name .....
transactions:
account_id | order_year | amount .....
our database is already very big (>300.000) accounts and transactions > 1.000.000
in our accounts list we now need a filter to search for accounts that had in sum of the amount more than XXXXEUR within year YYYY.
we already tried with subselect(inner Join and sum but our queries were much too slow.
maybe anybody can giv us some hints about how to make this query regarding performance.
thanks Pete
thanks for your fast answeres....
now we also have contacts that are related to accounts as follows:
account: id
contact: id
account_contact: id | contact_id | account_id
we also need the requested feature for searching within contacts.
so search for contacts that have (in sum) in year XXX more than YYYY EUR transactions.
many thanks for any help
Upvotes: 0
Views: 84
Reputation: 3533
So I worked up your relationship in SQL Fiddle http://sqlfiddle.com/#!2/3b45af/1/0 on MySQL 5.5, and populated it with approx. 250,000 accounts and 1,000,000 transactions. The analytic query only takes 1-2 seconds to run on the test data:
SELECT
accounts.id
, SUM(amount)
FROM accounts
JOIN transactions
ON (accounts.id = transactions.account_id)
WHERE order_year = 2005
GROUP BY accounts.id
HAVING SUM(amount) > 1000000;
The crucial part for performance is to make sure you have the tables properly indexed. accounts.id
you probably already have indexed implicitly because it should be a PK. The other index that helped was to have a non-unique index on transactions.order_year
:
CREATE INDEX transaction_year ON transactions(order_year);
The efficiency of the transaction_year
index will depend on how selective it is. If you have 20 years worth of transactions, then the index will help considerably. If you only have 1-2 years of transactions, the index might not help at all.
EDIT:
SELECT
accounts.id
, accounts.name
, SUM(amount)
FROM accounts
JOIN transactions
ON (accounts.id = transactions.account_id)
WHERE order_year = 2005
GROUP BY accounts.id, accounts.name
HAVING SUM(amount) > 1000000;
Upvotes: 0
Reputation: 1388
Try this:
select
Accounts.id,
sum(Transactions.amount) as TotalAmount
from
Accounts
inner join
Transactions on Transactions.account_id = Accounts.id
where
order_year = YYYY
group by
Accounts.id
having
sum(Transactions.amount) >= XXXX
Upvotes: 2