Optimize tables and database in MySQL automatically with PHP

posted in: MySql | 0

Summary:

Optimize Mysql Database Table:

The Following Command will show the present status of the Database Table

show table status like 'mytablename';

The following command will optimize the Database Table

optimize table mytablename;

Optimize Database in MySQL automatically with PHP  

The Following Script Will Optimize the entire database.
We don't have the direct command to optimize the entire database. 


$res = mysql_query('SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400');
while($row = mysql_fetch_assoc($res)) {
  mysql_query('OPTIMIZE TABLE ' . $row['Name']);
}
    Step1:   SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400
             In this step we will get the tables

    Step2:   It will optimize the each and every table individually

Previous Post
Next Post

Leave a Reply