Implementing an history with a limited number of entries with MySQL

On 11 May 2007 16:28:21 -0700, Salvatore Sanfilippo
<antirez@gmail.com> wrote:

>Hello All,
>
>I'm searching for the faster way (performance-wise) to create the
>following structure with mysql:
>
>I've a table where I take an history of things that happen in a
>system.
>This table contains just some integer and a creation timestamp in UNIX
>time
>Every time there is a new event to add to the history I just INSERT it
>
>The problem is that I want this history to contain at MAX 10000
>elements.
>Older elements should be removed in some way.
>
>What's the faster way to take the history limited in length?
>
>This is my best try so far.
>
>I take two max-length instead of just 10000, that I call low and high
>here where:
>
>high = 12000
>low = 10000
>
>high is a tollerance value, so I can do the following.
>
>- every time I've to insert a new element:
>- if the current history length > high
>- row = SELECT time FROM history ORDER BY time DESC LIMIT low,1
>- timelimt = row.time
>- DELETE FROM history WHERE time >= timelimit
>
>With the high/low trick I can do this queries just 1 time every 2000
>new history rows added,
>still I need all this to be very fast once it needs to be done.
>
>Of course time is INDEXed.
>
>Are there better ways to do this stuff?
>
>Regards,
>Salvatore

REPLACE the oldest one ?
Keep a variable n somewhere with the ID of the last changed, and
UPDATE the id n+1 (mod 10000) ?

subtenante wrote:
> On 11 May 2007 16:28:21 -0700, Salvatore Sanfilippo
> <antirez@gmail.com> wrote:
>
>> Hello All,
>>
>> I'm searching for the faster way (performance-wise) to create the
>> following structure with mysql:
>>
>> I've a table where I take an history of things that happen in a
>> system.
>> This table contains just some integer and a creation timestamp in UNIX
>> time
>> Every time there is a new event to add to the history I just INSERT it
>>
>> The problem is that I want this history to contain at MAX 10000
>> elements.
>> Older elements should be removed in some way.
>>
>> What's the faster way to take the history limited in length?
>>
>> This is my best try so far.
>>
>> I take two max-length instead of just 10000, that I call low and high
>> here where:
>>
>> high = 12000
>> low = 10000
>>
>> high is a tollerance value, so I can do the following.
>>
>> - every time I've to insert a new element:
>> - if the current history length > high
>> - row = SELECT time FROM history ORDER BY time DESC LIMIT low,1
>> - timelimt = row.time
>> - DELETE FROM history WHERE time >= timelimit
>>
>> With the high/low trick I can do this queries just 1 time every 2000
>> new history rows added,
>> still I need all this to be very fast once it needs to be done.
>>
>> Of course time is INDEXed.
>>
>> Are there better ways to do this stuff?
>>
>> Regards,
>> Salvatore
>
> REPLACE the oldest one ?
> Keep a variable n somewhere with the ID of the last changed, and
> UPDATE the id n+1 (mod 10000) ?

Or keep a timestamp field and replace the oldest one.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================