CODEWITHSUNDEEP
mysqldatabasejpajpql
Randnum
Randnum

Reputation: 1360

How to continuously remove anything older than the newst 10 entries of a MySQLdatabase (Possibly in JPQL/JPA)

I'm looking for a way to continuously monitor and delete the oldest entries so that the database is never larger than a certain value. I'm only interested in the latest 10 for example and everything past that number should be deleted. The database is updated through varous programs but the program that does the monitoring and deleting will probably be a Java EE application with JPA. I don't know at which layer of the implementation this will be done. If MySQL has build in management that does this, if I'll have to write a query that does this, or if there is a feature of Java that can do this.

Edit: I'm using an autoincremented id that could be used to determine threshhold of deleting.

Upvotes: 1

Views: 538

Answers (2)

JB Nizet
JB Nizet

Reputation: 692073

This is a complex problem, because unless your table is not linked to any other table, you might very well have the latest row in table A referencing a very old row in table B. In this case, although the table B's row is very old, you can't delete it without breaking the coherence of your database.

Doing it "continuously" is even harder (read: impossible). I would first

  • examine if it's really needed. Disks are cheap, and 10 entries in an enterprise database is really nothing.
  • implement some purge mechanism and execute it very now and then, when the database is not used by anyone else.

Upvotes: 3

Alex Norcliffe
Alex Norcliffe

Reputation: 2489

I'll have a stab without knowing anything about your table schema:

DELETE FROM MyTable WHERE Id NOT IN (SELECT TOP 10 Id FROM MyTable ORDER BY Date DESC)

This is pretty inefficient to run all the time and there may by a MySql-specific TRUNCATE that does the job nicer. You'd probably get better performance from limiting your reads to the 10 rows you need, and actually archiving / deleting the extraneous data only periodically.

Upvotes: 1

Related Questions