NYCPHP Meetup

[Hans Zaunere]

Jeff wrote:

> I have a particular table that has a very large number of deleted
> records (and approx. 500 records are deleted and added each day). The
> MySQL docs note that running an "Optimize" statement will clean up this
> potentially fractured file. However, I believe I had read somewhere (of
> course...don't ask me where "somewhere" is...I don't remember) that
> there is the potential that the table itself can become corrupted after
> running optimize. Is this true? And if it is true, how can I detect, via
> code, that a table has become hosed?

As Carlos points out, this used to be a problem.  On a 4.0.13 database, we delete/insert about 5000+ three times a day without issue.  The general progression we use:

-- LOCK TABLES as write (optional, but can't hurt if you can afford it)
-- DELETE QUICK the old rows
-- INSERT the new rows
-- ANALYZE, OPTIMIZE, FLUSH the table

Some of that is probably overkill, but it can't hurt, especially if you can afford the slight performance penalty.  With over 350,000 records in the table, we've had no problems doing this type of thing many times a day.

H