Move lots of records from one table to another in mysql using java

Question

I am trying to do some garbage collection in my database by moving records which are not required to graveyard tables. So if I have these four tables -

1. Version (id is primary key)
2. Version_history (version_id is foreign key)
3. Version_graveyard (Discarded Version rows here)
4. Version_history_graveyard (Discarded Version history rows here)

It consists of the following steps -

• Get all the version ids to be deleted.
• Notify everyone about the version ids to be deleted. (So I need to get the rows from version table before the actual move - Its a pretty complex select query)
• Do a Insert into Select from original Version table to the Version_graveyard table.(This would basically repeat the Select query I did in the previous step)
• Do another Insert into Select from original Version_history table to the Version_graveyard_history table. (Which would again repeat the select query I have already used)
• Delete data from the original tables (Using the same select query again.)

So as you can see this would require repeating the select query many times. I thought of getting all the versions from the first query and then using NamedParameterJdbcTemplate batchupdate function and In clause for all the subsequent queries like -

Insert into Version_Graveyard Select * from Version where id in (?, ?, ..... ?);

Delete from Version Where id in (?, ?, .. ?)

But the problem is that number of versions to be deleted could be many thousands (~20K on first run) and the according to documentation I can't have more than 1000 values inside the IN clause.

So what could be other way to avoid repeating the same select query for multiple steps.

Answer 1

It would possible to add a special field (flag) in the Version and Version_history table which indicates that the record should be next time moved to the graveyard tables. For example exportToGraveyard.

Next step is to do a UPDATE instead of a SELECT which set the field. All other querys can now access all records which should be moved by the flag.

For performance optimzation it would be also possible to add an INDEX on the field. But beware with an INDEX the UPDATE statement should be slower.

Answer 2

You could combine multiple IN clauses with OR.

 SELECT * FROM VERSION WHERE ID IN (...) OR ID IN (...) OR ID IN (...)