Sometimes it sucks to have to write a shell script... it can be alot easier to just go in through php...
Code:
$con = mysql_connect()
// I forgot the command to retrieve the table names so I am guessing...
$tables = get_a_list_of_all_the_tables($con);
foreach($tables as $a => $b){
$q = mysql_query("REPAIR TABLE `".$b."`",$con);
}
I did not try this code... I don't expect it to work... it's just for illustration...
PHP: mysql_list_tables - Manual
The best place to figure out more about optimizing tables is with the EXPLAIN statement... jmo...
You can prepend it to the beginning of your database query and mysql will return info about the query rather than the data you asked for...
"SELECT * FROM `table` WHERE `cat` = '2' ORDER BY `created`"
just add...
"EXPLAIN SELECT * FROM `table` WHERE `cat` = '2' ORDER BY `created`"
It will give you a good idea on which queries are overloading the server with table scans and which columns need keys on them.
MySQL :: MySQL 5.1 Reference Manual :: 12.3.2 EXPLAIN Syntax
It's probably not going to fix the overhead issue though... I don't know...
You can also dump a cron into linux via php as well... ( I forget the proper scheduling numbers )
system('0 0 0 0 5 wget -O -
http://domain.com/the_script_which_r...ir_command.php --user-agent cron');
http://ca2.php.net/system