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.
$all_tables = mysql_query("SHOW TABLES") or die(mysql_error());
$current_table = mysql_fetch_assoc($all_tables);
mysql_query("OPTIMIZE TABLE $current_table") or die(mysql_error());
} while ($current_table = mysql_fetch_assoc($all_tables));
Anyone have any ideas?
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?