Limiting a table to 10 records

[pbarney]

I need a guru's help here. What is the best query to have a table that contains only the latest 10 entries? I don't care if the previous entries get lost.

Specifically, I want to keep track of the latest 10 articles viewed on our website... but it seems that simply adding a new record and then querying the last 10 would eventually result in a bloated table that contains data that we simply will never use.

So I'm basically asking what the best way is to add an entry to the table and have the 11th oldest entry deleted.

Does this make sense?

Thanks for any help or direction here...

[pbarney]

The answer came from another forum:

here's a possible solution (but what Guelphdad says is correct, a table can have millions of regs without problems). You should use a TRIGGER as a checker with a condition previous to the insert (mysql >= 5.x), but, nowadays, triggers are very limited. An example of this would be (it should work, but no):

-------------------------------------------------------------------

mysql> SELECT * FROM articles;
+----+----------+
| id | nombre   |
+----+----------+
|  1 | art_1    |
|  2 | art_2    |
|  3 | art_3    |
|  4 | art_4    |
|  5 | art_5    |
|  6 | art_6    |
|  7 | art_7    |
|  8 | art_8    |
|  9 | art_9    |
| 10 | art_10   |
+----+----------+

mysql> DELIMITER //
mysql> CREATE TRIGGER del_row AFTER INSERT ON articles FOR EACH ROW
    -> BEGIN
    ->    SET @c:=(SELECT COUNT(*) FROM articles);
    ->    SET @m:=(SELECT MIN(id) FROM articles);
    ->    IF @c>=10 THEN
    ->       DELETE FROM articles WHERE id=@m;
    ->    END IF;
    -> END;
    -> //
mysql> DELIMITER ;
mysql> INSERT INTO articles(nombre) VALUES('art_11');
ERROR 1442 (HY000): Can't update table 'articles' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

-------------------------------------------------------------------


Instead, you have another solution. You can create a PROCEDURE which updates the table for you, so when you need to insert a new reg you only call it specifying the new values. An example:

-------------------------------------------------------------------

mysql> SELECT * FROM articles;
+----+----------+
| id | nombre   |
+----+----------+
|  1 | art_1    |
|  2 | art_2    |
|  3 | art_3    |
|  4 | art_4    |
|  5 | art_5    |
|  6 | art_6    |
|  7 | art_7    |
|  8 | art_8    |
|  9 | art_9    |
| 10 | art_10   |
+----+----------+

mysql> DELIMITER //
mysql> CREATE PROCEDURE act_row (newValue VARCHAR(50))
    -> BEGIN
    -> SET @c:=(SELECT COUNT(*) FROM articles);
    -> SET @m:=(SELECT MIN(id) FROM articles);
    -> IF @c>=10 THEN
    -> DELETE FROM articles WHERE id=@m;
    -> END IF;
    -> INSERT INTO articles(nombre) VALUES(newValue);
    -> END;
    -> //
mysql> DELIMITER ;
mysql> CALL act_row('art_11');
mysql> SELECT * FROM act_row;
+----+----------+
| id | nombre   |
+----+----------+
|  2 | art_2    |
|  3 | art_3    |
|  4 | art_4    |
|  5 | art_5    |
|  6 | art_6    |
|  7 | art_7    |
|  8 | art_8    |
|  9 | art_9    |
| 10 | art_10   |
| 11 | art_11   |
+----+----------+

-------------------------------------------------------------------


Then, if you have other users you can change their privileges (for example, not allowing to do INSERT on 'articles', instead of that, they must use your 'act_row' procedure).

Hope be helpful.

[rmwalters2000]

This could be completed a few ways. You could configure the add to also delete the first entered in. (IE place a simple auto-counter on it, then calc back to where the 11th item was and delete it with the the add.)

You could also do it as an end of day clean up. (IE locate the last item in the database and based on the auto-counter (again) and deleted everything lower than that.

Another one is if the data is date / time stamped, us a date / time calculation and figure it out that way also.

Personally, I would recommend an end of day type of deal but it depends if the server is yours and you can run something like that. If it belongs to a third party (IE renting web space from a provider) you might want to include it in the add feature.

Rick

[pbarney]

Thanks for answering! How do you do a delete when you add? I didn't even know that was possible. Or are you talking about a two-step process?

[rmwalters2000]

It would be a 2 step process but in the same process.

I will look into this and make sure that this is the correct process but here is what I am thinking.

Begin
Insert * from [......data.....]
Delete from [datatable.tablename] where ID = #### (or what ever format you use for this)
GO

The draw back with this is that is one fails, they BOTH fail so make sure that you have this configured well.


Rick