If I were doing it, I would write a script on the server that would
run at 2am (or whenever) to check the database and remove history.
Unless it's a priority to have history removed constantly on the go,
this is the best way to ensure it can be done properly and off peak
hours. I run several scripts under cronjobs to manipulate the database
at night.

Nino


On May 11, 4:28 pm, Salvatore Sanfilippo <anti...@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