Re: How to clean up database after deleting records

[Rick James]

FLUSH won't do it.

OPTIMIZE TABLE foo;
will squeeze out the dead space.

ALTER TABLE foo ORDER BY something;
does what OPTIMIZE does (but in a totally different way), plus clusters data based on the ORDER BY. In a few cases, this can be quite beneficial. Here's an example:
* Table is bigger than will fit in cache.
* Table has two ways to look at it, one is date oriented, the other is by some "key".
* There are hundreds of rows for each key (one per day)
* There are thousands of keys for each day.
* Data is coming in daily -- That is, new rows are appended to the table and/or filling in holes left by deleted rows.
* Your main query is SELECT ... WHERE key = '...' -- that is, one row per day.

Normally the rows for that SELECT will be quite scattered, and because of the size of the table, it will take a lot of disk hits.

The ALTER table will rearrange the rows so that the SELECT will find all the rows for the 'key' clustered in a few blocks, hence much less disk I/O.

IF this use of ALTER feels right for you, I recommend doing it monthly. Watch out: It could take hours (remember, it is bigger than will fit in cache).

For small tables (say, <100K rows), the ALTER is reasonably fast, but gives only limited improvement.

I have felt the need for the ALTER in less than 1% of the tables I have studied.