Optimise MySQL databases

optimise.pl

#!/usr/bin/perl
use DBI;

$dbi_host="localhost";

# all databases
@dbi_u=("admin_user");
@dbi_p=("qwerty");

#set the max count value
$ending_value = scalar(_at_dbi_u);

#loop throuogh each of the above databases
for($counter=0 ; $counter < $ending_value ; $counter++){
        # connect to data input table
        $dbh_in = DBI->connect("dbi:mysql:host=$dbi_host", $dbi_u[$counter], $dbi_p[$counter], { RaiseError => 0, AutoCommit => 1 }) or die "cannot connect to server" . $DBI::errstr;
        $sth_dbs = $dbh_in -> prepare("SHOW DATABASES");
        $sth_dbs -> execute();
       
        for($loop_db=0; $loop_db<$sth_dbs -> rows; $loop_db++){
                @row = $sth_dbs -> fetchrow_array;
       
                # databases with a '-' in their name are not handled very well
                if (index($row[0], '-') == -1){
                        # gen query
                        $sth = $dbh_in -> prepare("SHOW TABLES FROM ".$row[0]);
                        $sth -> execute();
                        $query = "";
       
                        for($loop_t=0; $loop_t<$sth -> rows; $loop_t++) {
                                @row2 = $sth -> fetchrow_array;
       
                                if ($query eq ""){
                                        $query = "OPTIMIZE TABLE ";
                                }else{
                                        $query .= ", ";
                                }
       
                                $query .= $row[0].".".$row2[0];
                        }
                       
                        # skip databases without tables
                        if ($query ne ""){
                                print "Optimizing ".$row[0]."...\n";
                                $sth = $dbh_in -> prepare($query);
                                $sth -> execute();
                        }
                }
        }
}