RE: [PHP-DB] Optimize Tables
| From: | Uzi Klein | Date: | Mon, 03 May 2004 10:32:03 +0000 |
| Subject: | RE: [PHP-DB] Optimize Tables | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-34249@lists.php.net to get a copy of this message | ||
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@towa.com.sg]
Sent: Monday, May 03, 2004 11:19
To: DBList
Subject: [PHP-DB] 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