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) ?