Hi David,
I hope you are well and that you had a good Christmas and New Year?
Could you perhaps point me in the right direction for a script to optimise only the tables in a database which require optimisation?
$sql = "OPTIMIZE TABLE `products`";Thanks,
Simon
I am able to optimise an
I am able to optimise an entire database, but on very large sites this would not be feasable.
$alltables = mysql_query("SHOW TABLES");while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
mysql_query("OPTIMIZE TABLE '".$tablename."'")
or die(mysql_error());
}
}
Regards,
Simon
Hi Simon, As I understand
Hi Simon,
As I understand it, a table only requires OPITIMIZE (ing) where there is an overhead; which is allocated but unused space within the storage of the table.
You can read this parameter using the SHOW TABLE STATUS query, which returns, amongst other fields, a "Data_free" column, which if greater than zero indicates that the table could benefit from being OPTIMIZED. As the result set of SHOW TABLE STATUS includes the table names, you can use it inplace of SHOW TABLES in your code; for example (untested):
<?php
$tablesToOptimize = array();
$alltables = mysql_query("SHOW TABLE STATUS");
while ($table = mysql_fetch_assoc($alltables))
{
if ($table["Data_free"] > 0)
{
$tablesToOptimize[] = $table["Name"]
}
}
foreach($tablesToOptimize as $tablename)
{
mysql_query("OPTIMIZE TABLE '".$tablename."'")
or die(mysql_error());
}
?>
Hope this helps!
Cheers,
David.