Viewing Thread:
"Optimizing MySQL tables"

Hi,

I was wondering...

Is there a way to optimize all the tables in a database in one hit, instead of going into the structure of each table and hitting Optimize?

Neil

Ahhh well - nice try.

Thanks anyway.

:(

N.

Ah yes sorry forgot you'd be restricted. You have to use the table optimisation feature in phpMyAdmin then I'm afraid.

Thanks for that code Eccles.

After one or two syntax corrections I got:

Query : Access denied for user '******'@'localhost' to database 'mysql'

(Username removed)

Neil

There is no single SQL statement that can do that in MySQL. However you could try this code:
<?php
set_time_limit(100);

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

// Connection variables edit for you VIP MySQL details
$host = 'localhost';
$user = 'vip_mysql';
$password = 'vip_mysql_password';
$vip_database_name = vip_database';

$dummy_db = 'mysql';
$db_link = mysql_connect($host,$user,$password);

$result = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `".$vip_database_name."`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while($rec = mysql_fetch_array($res)) {
if($rec['Data_free'] > 0) {
$to_optimize[] = $rec['Name'];
echo $rec['Name'] . 'needs to be optimized."n";
}
}
if(count($to_optimize) > 0) {
foreach($to_optimize as $tbl) {
mysql_db_query($vip_database_name, "OPTIMIZE TABLE `".$tbl."`", $db_link);
}
}

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo 'Optimisation completed. Total run time: '.$total_time.' secs'."n";
?>. I've not had time to test it so can't guarantee it will work! The usual warnings regarding using the code at your own risk apply of course!

Hi,

I was wondering...

Is there a way to optimize all the tables in a database in one hit, instead of going into the structure of each table and hitting Optimize?

Neil