Database Maintenance Script - Optimize your databas

💻 Development
1

I use this script after performing a lot of clean-up functions on my DB. If you haven’t optimized yours in a while, you should increase the sleep time a little bit.
Edit lines 4-7 with your info.
Upload the file to your server.
Use a browser to visit the URL of the script and it will execute.
It takes a while to run.

<?php
//    OPTIMIZES ALL TABLES IN YOUR DATABASE

$servername = "mydomain.com";        //ENTER YOUR DOMAIN NAME
$username = "mydbusername";            //ENTER YOUR DATABASE USERNAME
$password = "mydbpassword";            //ENTER YOUR DATABASE PASSWORD
$dbname = "mydbname";                //ENTER YOUR DATABASE NAME
$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
} else {
    $query = "SHOW TABLES;";
    $mytables = mysqli_query($conn, $query);
    while ($table = mysqli_fetch_assoc($mytables)){
        foreach ($table as $k => $v){
            $sql = "OPTIMIZE TABLE '".$v.";";
            mysqli_query($conn, $sql);
            sleep(5);
            echo "$v Optimized<br />";
        };
    };
    mysqli_close($conn);
}
?>
1 Like
2

Thanks Chris.

Dan

This will be very helpful.

3

IIUC, just to give another option, you can achieve the same using the mysqloptimize command

1 Like
5

Thanks Chris.

I’ll take to optimize my DB :cool:

1 Like