Table Optimize

This article is also available in the following languages:
By WyriHaximus
While developing a simple wiki bot with a queue table I noticed that sometimes the table kept it's old data as overhead. For that I searched through the bakery, manual and API but couldn't find anything about this. So this simple function was born.

Overview

Normally you would have a DBA to watch over it's database tables. But in small projects this isn't always the case and a self regulation application is a way to keep your database optimized. This function can aid with this task.

Code

Put this code in your app_model.php (create it if necessary).

Model Class:

<?php 
        public function optimize() {
                $db =& ConnectionManager::getDataSource($this->useDbConfig);
                $tablename = $db->fullTableName($this);
                if(!empty($tablename)) {
                        return $db->query('OPTIMIZE TABLE ' . $tablename . ';');
                } else {
                        return false;
                }
        }
?>

Usage

You can simply optimize a table in a similar as you would select/update/delete data from that table:

$this->TableName->optimize();

History:
Feb 6, 2009 applied suggestion by Rafael Bandeira + it now properly returns the result.

Comments

  • senseBOP
    Posted 06/07/09 10:49:55 AM
    Why not just do the following?

    Controller Class:

    <?php 
    function optimize() {
        return     $this->{$this->modelClass}->query('OPTIMIZE TABLE ' . $this->modelKey . ';');
    }    ?>
    It does what you want it to do, but without making you jump through flaming hoops and landing on broken pieces of glass. (Unless I missed something here...) :P
    Report this
  • rafaelbandeira3
    Posted 01/30/09 09:07:03 AM
    simple and good idea. But there's a correction to the code that must be applied:

    public function optimize() {
    if(!empty($this->useTable)) {
    $db =& ConnectionManager::getDataSource($this->useDbConfig);
    $db->query('OPTIMIZE TABLE ' . $db->fullTableName($this) . ';');
    }
    }
    Report this
  • dooltaz
    Posted 01/28/09 11:29:49 AM
    This is a good idea! Thanks.
    Report this

Comments are closed for articles over a year old