PHP MySQLi extension supports multiple queries which are concatenated by a semicolon. We can use this to optimize all MySQL tables in one single multi_query statement.
Table of Contents
show
PHP/MySQLi $mysqli->multi_query($query) to optimize database tables
By not querying SHOW TABLES FROM 'db_name' but by querying the special information_schema table, we can utilize mysqli multi_query to optimize all tables in a MySQL database. The entire query is grouped together using CONCAT / GROUP_CONCAT and the result is saved in a variable, which then is executed as a prepared statement.
MySQLi multi query, optimize all MySQL tables
Example code:
<?php
/**
* PHP-script to optimize all tables in a MySQL database
* - Jan Reilink
*/
define('DB_NAME', 'db_name'); // The name of the database
define('DB_USER', 'db_user'); // Your MySQL username
define('DB_PASSWORD', 'db_password'); // ...and password
define('DB_HOST', gethostbyname('mysql.example.com'));
$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())
{
/* divider is not necessary */
// printf("-----------------\n");
}
}
while ($mysqli->next_result());
}
$mysqli->close();
?>Summary
- PHP MySQLi extension allows multiple queries with a semicolon for optimizing MySQL tables.
- Using $mysqli->multi_query($query) helps execute these queries efficiently.
- Querying the information_schema table enables grouping with CONCAT or GROUP_CONCAT for optimization.
- The entire query is executed as a prepared statement for better performance.
Rate this post!