Talk In Code

You can optimize MySQL tables using the OPTIMIZE command. This can be used if you have a table with any variable length rows and you make lots of changes to the data in that table. Variable length columns are VARCHAR, VARBINARY, BLOB, or TEXT. The OPTIMIZE TABLE will defragment the data and reclaim any unused space. This also has the effect of resetting any auto incrementing numbers to the highest value in the table.

OPTIMIZE TABLE tbl_name;

You can do this for every table in the database, but to save time MySQL comes with a command line script that will optimize all tables in a database. This program is called mysqlcheck and can be run like this.

mysqlcheck -o database -u user -ppassword

Note that when writing this command you should not put a space in between the -p and your password.

The mysqlcheck program can also be used to check (-c/-m/-C), repair (-r) and analyze (-a) the tables in a database. These commands can be done by using the CHECK, REPAIR and ANALYZE MySQL commands.

Optimizing tables will clear out unnecessary settings and speed up your tables slightly, but there is little point in doing this more than once a month, or once a week for very high traffic MySQL servers.

By using the MySQL command.

SHOW TABLE STATUS FROM database;

You can get all sorts of information about a database. The query returns each table as a row and gives lots of information about each table. Using this query it is possible to work out some usage data for the database as a whole. The following function will take a database name and a database resource handle and return how big that database is, the number of tables, and the number of rows in those tables. You will probably have a maximum limit to the amount of data that you can store in your database, so this function is useful to make sure that you don’t exceed this limit. This function also uses another function found on the Talk In Code site called readableFileSize() to give more meaningful data sizes.

function getDatabaseInfo($database, $db, $readableSizes=true) {
 // set default return values
 $size = 0;
 $dataLen = 0;
 $indexLen = 0;
 $rows = 0;
 $tables = 0;
 // run SQL
 $result = mysql_query('SHOW TABLE STATUS FROM '.$database.';');
 if($result){
  // if result exsits then work out data values
  $tables = mysql_num_rows($result);
  while($data = mysql_fetch_assoc($result)){
    $size += ($data['Index_length'] + $data['Data_length']);
    $dataLen += $data['Index_length'];
    $indexLen += $data['Data_length'];
    $rows += $data['Rows'];
  };
 };
 // return
 if($readableSizes){
  return array('size'=>readableFileSize($size),'rows'=>$rows,'index-length'=>readableFileSize($indexLen),'data-length'=>readableFileSize($dataLen),'tables'=>$tables);
 }else{
  return array('size'=>$size,'rows'=>$rows,'index-length'=>$indexLen,'data-length'=>$dataLen,'tables'=>$tables);
 };
}

To run this function just connect to the database and run the function, passing the database name and the connection resource.

// open mysql connection
$handle = mysql_connect('localhost', 'root', 'wibble');
 
if(!$handle){
 die('Connection failed!');
}
 
echo '<pre>'.print_r(getDatabaseInfo('wordpress', $handle,false),true).'</pre>';

This prints the results.

Array
(
 [size] => 1835008
 [rows] => 1805
 [index-length] => 1277952
 [data-length] => 557056
)

Just to check I had a look at what the MySQL administrator application tells me about the database structure and found that they were the same.