MySQL Forums
Forum List  »  Backup

Re: How to clean up database after deleting records
Posted by: Rick James
Date: February 22, 2009 03:09PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to clean up database after deleting records
21590
February 22, 2009 03:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.