On May 12, 10:32 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Salvatore Sanfilippo 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
>
> Here's another way to think about it. Once you have 10000 rows, then every
> time you insert a new row, you can just delete the oldest one.
> So, if you're using MyISAM, you can check the result of SHOW TABLE STATUS
> and if it is 10000 then insert your row and delete the oldest one.


Or build a table with 10000 rows and then just UPDATE the oldest one
each time