Saturday, November 7, 2020

PHP / MySQL tables optimization

The php code below optimize all tables in MySQL database.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?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<br>";
 
$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<br>';
   
}
$connection->close();
echo 'Disconnected<br>';
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