MySQL Database cleaner 1.0 released

If you ever, and I think many of use DBAs have, been in the situation where you are stuck with data in the database that isn't used and isn't accessed, data which may consist of rows that are no longer used, data rows that aren't references, because you don't use FOREIGN KEYs or they weren't applicable in this case. Or data that was once used, but no longer is.

And in many cases, this data is tucked in among your other good rows of data :-( One way of cleaning up the database in a case like this is to run standard DELETE statements, but there are a few issues with this:

• You may be accessing a lot of data, so this may take a while.
• You will be locking large amount of data for this.
• The join statement to get the data that is no longer used and / or no longer referenced is complex.
• There is no really good way to split this DELETE in smaller chunks, except using LIMIT, but if what takes a long time to execute if the complex join to find offending rows, this is not always useful.

What is needed is a cleaner. A program that can run a simple or complex SELECT statement to find identifiers of the data you want to delete, and as this is a SELECT, we will have less issues with long running transactions and database locking. And the the IDs gotten from this SELECT is used to DELETE the rows we want to delete, based on the PRIMARY KEY to make it fast and to minimize the time we lock things.
In addition, we would prefer the DELETE to run in multiple threads and for the program to be very configurable. Well, here is the fix for you: mycleaner. MyCleaner is a program that does just this. It is not very complex, but useful.

Before I point you to the download location, I have one thing that is on the TODO list but which is not yet fixed: MyCleaner only works with integer numeric keys (INT, SMALLINT, BIGINT etc). If you are find with this and have a need to clean your data, give MyCleaner a try, it 's GPL Open Source and is ready for download from sourceforge here.

Ideas for improvement, code contributes etc are welcome. And yes, there is also documentation, not the most comprehensive of documentation, but it is there.

/Karlsson