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-script to optimize all tables in a MySQL database
 * - Jan Reilink, Twitter: @Jan_Reilink
 * Please donate:

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( '' ) );
// why gethostbyname()? See

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

/* one multi query over three lines */
  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] );
    if ( $mysqli->more_results() ) {
      /* do something here */
  while ( $mysqli->next_result() );


This will make your MySQL database smaller and faster.

Show 4 Comments


  1. Andy

    Hi, i think i made a big mistake.
    I used this code on my wordpress website but my now all queries are very slow . Is there a possibility to change it back ?

    • Hi Andy,
      It would seem very unlikely that a OPTIMIZE TABLE statement makes your queries very slow. Maybe there is something wrong at the database configuration level. Clear your WordPress caches, flush your rewrite rules and try again. If it’s still slow, you can dive in using plugins like Query Monitor, Log HTTP Requests, Health Check & Troubleshooting (the plugin, not the core functionality!) and perhaps Advanced Cron Manager. And if that doesn’t offer a solution, you’d best ask your hosting provider for assistance.

      • Andy

        To make sure i dont take it wrong. To use it i have to upload the php and trigger it once right ?

Leave a Reply

Your email address will not be published. Required fields are marked *