NYCPHP Meetup

[nycphp-talk] MySQL table optimization

Jeff jsiegel1 at optonline.net
Fri Aug 29 12:06:09 EDT 2003


Hans,

I'm using MySQL 3.23.56 (the ver with RH 7.3). Based on Carlos' message,
the known "bug" shouldn't be in this version.

The insert is done during the course of the day - not all at once - so I
can skip that step. This gives me a GREAT outline of steps (THANKS!!!).
I'll share some code once it's ready.

Jeff

-----Original Message-----
From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
On Behalf Of Hans Zaunere
Sent: Friday, August 29, 2003 10:43 AM
To: NYPHP Talk
Subject: Re: [nycphp-talk] MySQL table optimization




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

_______________________________________________
talk mailing list
talk at lists.nyphp.org
http://lists.nyphp.org/mailman/listinfo/talk




More information about the talk mailing list