Use a cron job....
Like:
0 0 * * * php Optimize.php > /dev/null
This is an example for optimizing every table in all databases on your mysql
server.
Optimize.php :
<?php
$db = mysql_connect("host", "user", "pass");
$sql = "SHOW DATABASES";
$dbs_result = mysql_query($sql, $db);
if(mysql_num_rows($dbs_result))
{
while($dbs_row=mysql_fetch_assoc($dbs_result))
{
$database = $dbs_row["Database"];
echo "\n\nOptimizing database $database : \n";
mysql_select_db($database, $db);
$sql = "SHOW TABLE STATUS";
$tbls_result = mysql_query($sql, $db);
if(mysql_num_rows($tbls_result))
{
while($tbls_row=mysql_fetch_assoc($tbls_result))
{
$TableName = "`".$tbls_row["Name"]."`";
$sql = "REPAIR TABLE ".$TableName;
echo "\n".$sql;
mysql_query($sql, $db);
$sql = "OPTIMIZE TABLE ".$TableName;
echo "\n".$sql;
mysql_query($sql, $db);
}
}
}
}
echo "\n\n";
mysql_close($db);
?>
-----Original Message-----
From: Ng Hwee Hwee [mailto:hhwee@xxxxxxxxxxx]
Sent: Monday, May 03, 2004 11:19
To: DBList
Subject: Optimize Tables
Hi,
Is there a way to create a schedule to optimise my MySQL tables
automatically? The overhead of some of my tables can reach 355,548 bytes
over 3 working days!
Please help me!
Thanks.
Hwee
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
