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.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.