Trouble optimizing all tables in a database

[Morthian]

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.

//OPTIMIZE TABLES
$all_tables = mysql_query("SHOW TABLES") or die(mysql_error());
$current_table = mysql_fetch_assoc($all_tables);
do {
	mysql_query("OPTIMIZE TABLE $current_table") or die(mysql_error());
} while ($current_table = mysql_fetch_assoc($all_tables));

Anyone have any ideas?

[Dan_Grossman]

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]

@Dan_Grossman

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?