CODEWITHSUNDEEP
mysqldatabasejpajpql
Randnum
Randnum

Reputation: 1360

Most efficient way to periodically delete all entries in database older than a month

I'm trying to most efficiently manage a database table and get rid of old entries that will never be accessed. Yes they could probably easily be persisted for many years but I'd just like to get rid of them. I could do this maybe once every month. Would it be more efficient to copy the entries I want to keep into a new table then simply delete the old table. Or should a query manually delete each entry after that threshhold that I set.

I'm using MySQL with JPA/JPQL JEE6 with entity annotations and Java persistence manager.

Thanks

Upvotes: 0

Views: 1235

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562721

Another solution is to design the table with range or list PARTITIONING, and then you can use ALTER TABLE to drop or truncate old partitions.

This is much quicker than using DELETE, but it may complicate other uses of the table.

Upvotes: 1

Vivien Barousse
Vivien Barousse

Reputation: 20885

A single delete query will be the most efficient solution.

Copying data from one database to another can be lengthy if you have a lot of data to keep. It means you have to retrieve all the data with a single query (or multiple, if you want to batch), and issue a lot of insert statements in the other database.

Using JPQL, you can issue a single query to delete all old statements, something like

DELETE FROM Entity e WHERE e.date = ?

This will translated to a single SQL query, and the database will take care of deleting all the unwanted records.

Upvotes: 0

Related Questions