• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Optimize tables in MySQL automatically with PHP

Optimize tables in MySQL automatically with PHP

In previous posts I looked at how to optimize a MySQL table from the MySQL command line interface and from phpMyAdmin by using the optimize [tablename] command to free up unused space. In this post I will look at how to do this with a PHP script which could be run periodically to optimise all non-optimal MySQL tables.

The SQL we’ll use to find tables which are non-optimal looks like this:

SHOW TABLE STATUS WHERE Data_free > [integer value]

substituting [integer value] for an integer value, which is the free data space in bytes. This could be e.g. 102400 for tables with 100k of free space. This will then only return the tables which have more than 100k of free space.

An alternative way of searching would be to look for tables that have e.g. 10% of overhead free space by doing this:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1

The downside with this is that it would include small tables with very small amounts of free space so it could be combined with the first SQL query to only get tables with more than 10% overhead and more than 100k of free space:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400

Using the above SQL, the PHP code would look like this:

$res = mysql_query('
  SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400
');

while($row = mysql_fetch_assoc($res)) {
  mysql_query('OPTIMIZE TABLE ' . $row['Name']);
}

And that’s all there is to it. You could then run this PHP code snippet within a full PHP script and run it via cron once per day.

Check Out These Related posts:

  1. MySQL utility commands
  2. Optimize a table in MySQL from phpMyAdmin
  3. Load data into MySQL with foreign key constraint issues Part 2
  4. Automatically append or prepend files in a PHP script

Filed Under: MySql, PHP

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap