List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 29 2002 4:55pm
Subject:Re: Stored Procedureish Advise needed
View as plain text  
In the last episode (Aug 29), John Gruber said:
> I have an application (radius) that is logging network activity to
> MySQL. When a users completes a network session an accounting insert
> query is made for that session showing the session length.  The
> application has the functionality to SUM all session times for a user
> and determine if it should let them connect the next time.  The SUM
> query will eat us up as the number of session in the table is very
> large.  If I had stored procedures (and triggers would make this so
> nice!) I would simply add the seconds every time I got an accounting
> update. This would reduce my overhead 1000 fold. I can alter the
> INSERT query for the application, but the application will only let
> me issue ONE query.

You'll have to change your application, then :)

> I.O.W turn the following into one query for me!
> 
> INSERT INTO SOMETABLE (val1,val2,val3) VALUES ('val1','val2','val3')
> 
> and (psuedo code.. which makes now sense!)
> 
> UPDATE SOMEOTHERTABLE SET (SECONDS = SUM('val1' + SECONDS))

You can always run an hourly total script that walks through the
accounting records added in the last hour and updates the user records
in SOMEOTHERTABLE.  Or even run it every minute.
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
Stored Procedureish Advise neededJohn Gruber29 Aug
  • Re: Stored Procedureish Advise neededDan Nelson29 Aug
  • Re: Stored Procedureish Advise neededGelu Gogancea29 Aug