Optimising my tables

[ahmedtheking]

As we use mysql tables more and more, an overhead builts. What I had done with my site was add a little code that optimises all my tables on any page that's requested. Well that was a huge strain on the server, so i've made a cron. But I can't be bothered to write out the same code for each of my tables, so i thought of using the following code to get my tables and then optimising them:

while ($tablenames = mysql_fetch_row(mysql_list_tables(DBNAME,LINK))) {
// optimise this table
//mysql_query("OPTIMIZE TABLE ".$tablenames[0],LINK) or die (mysql_error());
}

But, it only optimises the first table. I did a print_r() on 'mysql_fetch_row(mysql_list_tables(DBNAME,LINK))' and that only displays the first table, what's wrong here?

[eelixduppy]

Hello...

i woudl try something like this:

$result = mysql_list_tables($db, $link);
$num_rows = mysql_num_rows($result);
for($i = 0; $ < $num_rows; $i++)
{
$table = mysql_table_name($result, $i);
echo "$table OPTIMIZED!<br>";
mysql_query("OPTIMIZE TABLE ".$table,$link) or die (mysql_error());
}

i really hope this works. i haven;t tried it fully yet but it looks good..

good luck

eelix