List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:December 5 2004 3:13pm
Subject:Re: More efficient way?
View as plain text  
No help for 3.23, but in 4.1 you could use

   INSERT INTO dailycounters (trackingdate, thingid, count)
     VALUES (<today>, <somerecordid>, 1)
   ON DUPLICATE KEY UPDATE count = count + 1;

assuming a UNIQUE index on the combination of trackingdate and thingid.

Michael

Jim McAtee wrote:

> 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)
>>>   VALUES
>>>     (<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.
> 
> 
> 
Thread
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