Saturday, November 7, 2020

PHP / MySQL tables optimization

The php code below optimize all tables in MySQL database.


<?php
echo '< html > < body >';
$servername = "*****";
$username = "*****";
$password = "*****";
$database = "****";

// Create connection
$connection = new mysqli($servername, $username, $password, $database);

if ($connection -> connect_errno) {
  echo "Failed to connect to MySQL: " . $connection -> connect_error;
  exit();
}
echo "Connected successfully
"; $sql = "show tables"; if (!$result = $connection->query($sql)) { die ('There was an error running query[' . $connection->error . ']'); } while ($row = $result->fetch_array()) { $table_name = $row[0]; echo 'Optimizing table: '.$table_name.'...... '; $connection->query("OPTIMIZE TABLE '".$tablename."'"); echo ' ....done
'; } $connection->close(); echo 'Disconnected
'; die("End"); echo '< body > < html >'; ?>

Script execution output on Prestashop 1.6 database will be like:

Connected successfully 
Optimizing table: pequi_access...... ....done 
Optimizing table: pequi_accessory...... ....done 
Optimizing table: pequi_address...... ....done 
Optimizing table: pequi_address_format...... ....done ....... 
Optimizing table: temp_attr...... ....done 
Disconnected
End

No comments:

Post a Comment