We have a set of INODB tables (auto-increment-columns) that grow at rapid rates. The tables are filled by a java aplication that does massive inserts. After a certain amount of time these tables contain 4 million rows and are 1 GIG big. I conducted substantial testing and noticed that things started to slow down after these 4 million rows had built up.

What I would like to do, is to shift /split or rename these tables and create new empty tables, so that the java aplication can continue to insert with as little as possible delay.

Does anyone knows a way of doing this?

Thanks

OpenT

Create and Rename will do it.

First create a new table. Check that everything is fine.

Then (when there are no users) do a rename:

RENAME TABLE old_table TO history_table, new_table TO old_table;

As this operation is atomic, this operation will be a success, or it'll leave everything as it was before.

Cheers, Bill