Optimize All Mysql Tables in one Database with PHP mysqli (MySQL Improved Extension)

Posted by Shek on September 30, 2013 under MySQL, PHP | Be the First to Comment | Total View: 6,475 views

In this post, I am going to talk about how to optimize all mysql tables in database in one go with php mysqli (MySQL Improved Extension)


I mentioned similar topic before in my past post Optimize Mysql Table with PHP script which I was using the mysql function of php to accomplish the job. However as the mysql function of php is deprecated as of PHP 5.5.0 (reference from http://www.php.net) and therefore I have moved onto the new MySQL Improved Extension.

Here is how to do so:

First we create a connection with mysqli_connect() function:

1
2
/* replace the parameters in the function with the details of the one which you want to optimize */ 
$connection = mysqli_connect("hostname","mysql_user","mysql_password","mysql_database_name");

Then we get all the tables’ name from the database by running a query “SHOW TABLES” with function mysqli_query():

1
$resultSet = mysqli_query($connection,"SHOW TABLES");

Then we loop through the tables’ name and optimize each table:

1
2
3
4
while ($eachTable = $resultSet->fetch_row() )
{ 
mysqli_query($connection,"OPTIMIZE TABLE ".$eachTable[0]); 
}

All together for the php file:

1
2
3
4
5
6
$connection = mysqli_connect("hostname","mysql_user","mysql_password","mysql_database_name"); 
$resultSet = mysqli_query($connection,"SHOW TABLES"); 
while ($eachTable = $resultSet->fetch_row() )
{
mysqli_query($connection,"OPTIMIZE TABLE ".$eachTable[0]); 
}

This file can be run as a cronjob which the system could preform optimize table in a regular basis.

Not what you want? Try a Search

Add A Comment

*