Optimize all MySQL tables with PHP/MySQLi multi_query

Photo of author
Written By Jan Reilink

Windows Server systems administrator & enthusiast.

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 define( 'DB_NAME', 'db_name' ); define( 'DB_USER', 'db_user' ); define( 'DB_PASSWORD', 'db_password' ); define( 'DB_HOST', gethostbyname( 'mysql.example.com' ) ); $mysqli = new mysqli( DB_HOST,DB_USER,DB_PASSWORD, DB_NAME ); $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 { if ( $result = $mysqli->store_result() ) { while ( $row = $result->fetch_row() ) { printf( "%s\n", $row[0] ); } $result->free(); } if ( $mysqli->more_results() ) { } } while ( $mysqli->next_result() ); } $mysqli->close(); ?>
Code language: PHP (php)

This will make your MySQL database smaller and faster.


Did you like: Optimize all MySQL tables with PHP/MySQLi multi_query

Then please, take a second to support Sysadmins of the North and donate!

Your generosity helps pay for the ongoing costs associated with running this website like coffee, hosting services, library mirrors, domain renewals, time for article research, and coffee, just to name a few.



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 ?

    Reply

Hi! Join the discussion, leave a reply!

%d bloggers like this: