Adding table indexes on Silverstripe DataObjects

silverstripe

Indexes can the database make some queries faster. In this post I'll explain how to add them to a SilverStripe site.

A word of caution, the index types (btree, hash, unique, etc) are unique per database, so might need adjust to the specific adapter that your are using. The examples below are using the MySQLDatabase adapter.

Database tuning is craft and careful profiling and monitoring can help you out. Tools like xhprof and databases explain queries are the tools of the trade. Don't smack indexes all over the place because it defeats the purpose.

if you're not part of the solution, you're part of the problem.

Adding index on your DataObjects

Here is an example on how to add one index on the Email column and one combined index on Email and ID columns.

<?php
class NiceGuy extends Member {

    /**
     * Will effectively add an index on the Email column in the database
     * This will improve lookups and sorting on Email
     */
    public static $indexes = array(
        // Just smack a btree index on Email
        'Email' => true,
        // Combined index for Email and ID
        'Email_ID' => '(Email,ID)',
    );
}

Following is a slightly more specific way of telling the database adapter what kind of index it should create.

<?php
class WiseGuy extends DataObject {

    public static $db = array(
      'LicenseNumber' => 'Int'
    );

    /**
     * Will make sure that the combined ID and LicenseNumber is unique
     */
    public static $indexes = array(
        'ID_LicenseNumber' => array('type'=>'unique', 'value'=>'ID,LicenseNumber')
    );
}

Other types that the MySQLDatabase adapter supports:

Adding index with a DataObjectDecorator in 2.4

Indexes like db, has_one and so on can be modified via DataObjectDecorators. This opens up the possibility to add indexes on "core" classes.

<?php
class EmailDecorator extends DataObjectDecorator {
  
  public function extraStatics() {
    return array(
      'indexes' => array(
        // Making a combined index
        'email_firstname' => '(Email,Firstname)'
      )
    );
  }
}

Adding index with a DataExtension in 3.0

You should not use the extraStatics method anymore as it was deprecated in 3.0. Instead define the indexes as you would define them in a DataObject.

<?php
class EmailExtension extends DataExtension {

    public static $indexes = array(
        'Email_ID' => array('type'=>'index', 'value'=>'Email,ID')
    );
}

For more information read the source for the MySQLDatabase::convertIndexSpec and MySQLDatabase::getIndexSqlDefinition methods.