0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Arthur Robaeys
  • 327
  • 3
  • 15

2 Answers2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • hi, thanks for the answer. Was already using limit=10 , but i was just scared that it might be bad filling the database up while i only actually display 10 records. – Arthur Robaeys Jun 10 '19 at 15:38
  • 1
    @ArthurRobaeys . . . Remember the index! If this does really get to be big (like millions of rows), you might want to schedule a job to remove older values, if you are sure you won't ever need them. – Gordon Linoff Jun 10 '19 at 15:57
0

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.

Scott Jacobsen
  • 975
  • 5
  • 16
  • MySQL didn't support LIMIT for IN subqueries? Did they fix that in any version? – Paul Spiegel Jun 10 '19 at 16:33
  • You're right. I didn't have mysql handy when answering and forgot. Here's how to work around that https://stackoverflow.com/questions/12810346/alternative-to-using-limit-keyword-in-a-subquery-in-mysql – Scott Jacobsen Jun 10 '19 at 18:04