At the end of a daily cron job, a php script runs the code below to optimize all tables in a database. The script produces no errors, but seems to fail at optimizing the tables; I have noticed that the overhead size of my tables has been increasing.

When you run long queries (OPTIMIZE can take a long time) in a loop like this, you sometimes lose your connection. I'm sure there's some explanation for that, but I've run into it enough with scheduled jobs just like this that I simply don't write code like that anymore.

Instead, fetch all the table names into an array, then create a separate loop which opens the connection, runs OPTIMIZE [table] and closes the connection each time.

Morthian
—
2010-01-05T09:30:15Z —
#3

Dan_Grossman said:

When you run long queries (OPTIMIZE can take a long time) in a loop like this, you sometimes lose your connection. I'm sure there's some explanation for that, but I've run into it enough with scheduled jobs just like this that I simply don't write code like that anymore.

Instead, fetch all the table names into an array, then create a separate loop which opens the connection, runs OPTIMIZE [table] and closes the connection each time.

Alright, thanks, I'll give that a try and see how it goes.

By the way, the same script deletes outdated records from about a dozen tables before the optimizing. All of those DELETE queries should be fine to run in a single connection, right?