Mario Splivalo wrote:
Hello again.
I have to track user subscriptions to certain mailinglists, and I also
need to track credits users have on those mailinglists. On one side I
have procedures that add credits, on other side I have procedures that
subtract available credits. Add/subtract is pretty intensive, around
30-50 adds per minute (usualy 10 or 100 credits), and around 200-500
subtracts per minute (usualy by one or two credits).
I have created table user_subscriptions to track user subscriptions to
certain mailing list. I have derived subscription_id as primary key. I
have two other tables, user_subscription_credits_given, and
_credits_taken, wich track credits for subscription added or subtracted
to or from certain subscription. I created those two tables so I could
eliminate a lot of UPDATES on user_subscriptions table (if I were to
have a column 'credits' in that table).
It sounds to me like you have decided beforehand that the obvious
solution (update a credit field in the user_subscriptions table) is not
going to perform well. Have you tried it? How does it perform?
If it does indeed give you performance problems, you could instead run
some kind of batch job to update the credits field (and delete the
/given/taken records).
Finally: You could refactor the query to get rid of the union:
SELECT u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from,
u.valid_to, (
SELECT sum(credits)
FROM credits_given
WHERE subscription_id = u.subscription_id
) - (
SELECT sum(credits)
FROM credits_taken
WHERE subscription_id = u.subscription_id)
) AS credits
FROM user_subscriptions u
WHERE u.user_id = 1
(Not tested).
You will probably need a COALESCE around each of the subqueries to avoid
problems with nulls. <rant>The sum of an empty set of numbers is 0. The
conjunction of an empty set of booleans is true. The SQL standard
somehow manages to get this wrong</rant>
/Nis
