Optimize all MySQL tables with PHP/MySQLi multi_query

The PHP MySQLi extension supports multiple queries, which are concatenated by a semicolon, with mysqli->multi_query. We use this to optimize all MySQL tables, in a single multi-query statement. Neat! Optimizing MySQL tables is important to keep tables small and fast. This boosts MySQL, PHP and website performance and we all love that, don’t we? :)

Optimize and tune MySQL database performance

by querying the special information_schema table

You can use mysqli multi_query to optimize all tables in a MySQL database: the entire query is grouped together using MySQL CONCAT and GROUP_CONCAT, and the result is saved in a variable.

This variable is then executed as a prepared statement.

MySQLi multi_query, optimize all MySQL tables

PHP example code:

<?php
/**
 * PHP-script to optimize all tables in a MySQL database
 * - Jan Reilink, Twitter: @Jan_Reilink
 * 
 * Please donate: https://www.paypal.me/jreilink
 */

define( 'DB_NAME', 'db_name' );    // The database name
define( 'DB_USER', 'db_user' );     // Your MySQL username
define( 'DB_PASSWORD', 'db_password' ); // ...and password
define( 'DB_HOST', gethostbyname( 'mysql.example.com' ) );
// why gethostbyname()? See https://www.saotn.org/php-mysql-and-ipv6-still-slow/

$mysqli = new mysqli( DB_HOST,DB_USER,DB_PASSWORD, DB_NAME );

/* one multi query over three lines */
$query = "SELECT CONCAT('OPTIMIZE TABLE ',
  GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') 
  INTO @optimizecmd FROM information_schema.tables
  WHERE table_schema=database(); 
  PREPARE s1 FROM @optimizecmd; EXECUTE s1;";

if ( $mysqli->multi_query( $query ) ) {
  do {
    /* store first result set */
    if ( $result = $mysqli->store_result() ) {
      while ( $row = $result->fetch_row() ) {
        printf( "%s\n", $row[0] );
      }
      $result->free();
    }
    if ( $mysqli->more_results() ) {
      /* do something here */
    }
  }
  while ( $mysqli->next_result() );
}

$mysqli->close();
?>Code language: PHP (php)

This will make your MySQL database smaller and faster.