List:General Discussion« Previous MessageNext Message »
From:Jim McAtee Date:December 5 2004 6:30am
Subject:Re: More efficient way?
View as plain text  
Ah, I see what you're saying.  Each event as it happened might be a simple 
insert into a temporary table and then I could batch the total daily 
activity into a daily record at the end of the day.  A classic size vs. 
speed tradeoff.

I was just hoping there might be a (My)SQL way to say "update record if 
found, otherwise create one" in one query.

----- Original Message ----- 
From: "Eric Bergen" <eric.bergen@stripped>
To: "Jim McAtee" <jmcatee@stripped>
Cc: <mysql@stripped>
Sent: Saturday, December 04, 2004 2:40 PM
Subject: Re: More efficient way?

> Depending on how your application works you might be able to batch the
> daily updates. Example only do an update every 20 items instead of for
> each one. Alternatly you could run an update with a join every 10
> minutes or so that would update the daily counter.
> On Thu, 2 Dec 2004 20:56:45 -0700, Jim McAtee <jmcatee@stripped> 
> wrote:
>> I have an application which keeps a table of daily event counters 
>> related
>> to other records in a databse.  Since the trackingrecords are kept on a
>> daily basis new records are created each day for items being 
>> referenced.
>> In pseudo-code:
>> // Check for the existance of daily tracking record
>> SELECT dailycounterid
>> FROM dailycounters
>> WHERE trackingdate = <today>
>>   AND thingid = <somerecordid>
>> if <query.recordwasfound>
>>   // If it exists, increment counter
>>   UPDATE dailycounters
>>   SET count = count + 1
>>   WHERE dailycounterid = <query.dailycounterid>
>> else
>>   // Otherwise add new record with count of 1
>>   INSERT INTO dailycounters
>>     (trackingdate, thingid, count)
>>     (<today>, <somerecordid>, 1)
>> endif
>> Is there any way to do this with a single MySQL query instead of a 
>> check
>> followed by either an insert or an update?  The MySQL version is 3.2x
>> using MyISAM tables.

More efficient way?Jim McAtee3 Dec
  • Re: More efficient way?Eric Bergen4 Dec
  • Re: More efficient way?Jim McAtee5 Dec
    • Re: More efficient way?Michael Stassen5 Dec