skip to Main Content

i have a small question. Is it possible if you have a database, that you can keep only the last 10 records inserted in the table? Or that your table can only hold 10 records, so when a new record is inserted, it adds it but gets rid of the last inserted record?

I’m using mySQL and PhpMyAdmin.

2

Answers


  1. You can do this, using a trigger.

    I don’t think I recommend actually doing this. There is overhead to deleting records. Instead, you can just create a view such as:

    create view v_table as
        select t.*
        from t
        order by insertionDateTime desc
        limit 10;
    

    This might seem expensive, but with an index on (insertionDateTime desc), the performance should be reasonable.

    Login or Signup to reply.
  2. Here’s one way:

    delete from my_table where id not in (select id from my_table order by id desc limit 10);

    Assuming id is a number field that is incremented on each insert. Timestamps would also work.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search