You are here: Saotn.org » Code base » Optimize all MySQL tables with PHP/MySQLi multi_query

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: @HertogJanR * * 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(); ?>

This will make your MySQL database smaller and faster.

4 thoughts on “Optimize all MySQL tables with PHP/MySQLi multi_query”

  1. 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 ?

    1. 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.

        1. Sorry for my late reply Andy.

          You can trigger this script as many times as you like. It almost can’t do any wrong (except in some very rare occasions).

          In this post (https://www.saotn.org/optimize-wordpress-mysql-tables-cron/) I have a WordPress plugin doing the same utilizing WP_Cron, so it gets triggered hourly or daily.

          I also co-authored the Vevida Optimizer plugin, that has the same functionality (among others, like converting MyISAM tables to InnoDB, settings for automatic updates, enz): https://wordpress.org/plugins/vevida-optimizer/.

Leave a Reply

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