truncate through limited table entries.

[Phil Jackson]

Hi I was wondering if there was a simple solution in mysql to truncate mysql entries.

i.e.

if i want only a maximum of 100 entries in one table if a new entry is submitted, (101st entry) then it would delete lets say the last entry ( ordered by a time stamp ) and insert the new one (still only having 100 entries).

Any help much appreciated,

Regards, Phil

p.s. I have heard I may be able to use 'triggers'??? anyone know how to use triggers in this example or a GOOD tutorial on 'triggers'??

[guelphdad]

Why not simply use a timestamp column and then retrieve the 100 newest rows?

[Phil Jackson]

because each user has a table storing actions that they do on the site. If a really active user, there could be 100 entries a day.

[guelphdad]

1) you don't need a new table for each user
2) properly indexed tables, you aren't worrying about performance problems until you get into the 10s of millions of records per table and even then they aren't all that large.

[Old Pedant]

I'm going to second GuelphDad but go *much* stronger:

If you have one table per user, you have a really screwed up database design. You are surely causing yourself much more grief by doing this than you could possibly be saving.

Anyway, the answer to your original question is simple: Use a Stored Procedure to add the entries.

I do agree with GuelphDad that there's no real reason to remove the entries to limit people to 100. MySQL will quite handily keep track of millions of records and if you just do LIMIT to get the latest 100, ordered by datetime posted perhaps, you really will keep it as simple and as efficient as possible.

But if you have some other odd reason to limit to 100, then a Stored Proc is clearly the way to go.

DELIMITER $$
CREATE PROCEDURE add_post
(
    i_userid INT,
    i_post TEXT
)
BEGIN
    DELETE FROM posts WHERE userid = i_userid AND postid NOT IN (
        SELECT postid FROM posts WHERE userid = i_userid 
        ORDER BY whenposted DESC LIMIT 100 );
    INSERT INTO posts ( userid, whenposted, message )
    VALUES( i_userid, NOW(), i_post );
END
$$
DELIMITER ;

And notice how easy this is, with all the posts in a single table instead of using one table per user. If you did use one table per user, the code would be MUCH more complex.