ORM performance#

Indexes#

You can define indexes for your ORM queries using the $indexes configuration property in your DataObject subclasses. See the Indexes section for more information.

TreeDropdownField SearchFilter configuration#

The TreeDropdownField uses a PartialMatchFilter by default to match against records. Indexes aren't effective when this filter is used, so you may find this field is slow with large datasets.

You can configure the field to use a different filter (such as StartsWithFilter) using the TreeDropdownField.search_filter configuration property:

CopySilverStripe\Forms\TreeDropdownField:
  search_filter: 'StartsWith'

See SearchFilter Modifiers for more information about search filters.

Skipping check and repair when the database is built#

When you run sake db:build, there is a step that checks the integrity of the database tables (via CHECK TABLE) and repairs issues (via REPAIR TABLE) if possible.

For tables with many records (tens/hundreds of thousands) this can be slow. If you identify that you have some specific DataObject models with lots of records which are slowing down building the database, you might want to explicitly skip checks for those:

CopySilverStripe\ORM\Connect\DBSchemaManager:
  exclude_models_from_db_checks:
    - App\Model\ModelWithManyRecords

You can also disable these checks entirely:

CopySilverStripe\ORM\Connect\DBSchemaManager:
  check_and_repair_on_build: false

You can always manually trigger a check and repair (e.g. in a BuildTask) by calling DB::check_and_repair_table(). This ignores the above configuration.

Changing ClassName column from enum to varchar#

On websites with very large database tables it can take a long time to run dev/build, which can be a problem when deploying changes to production. This is because the ClassName column is an enum type which requires an a ALTER TABLE query to be run affecting every row whenever there is a new valid value for the column.

For a very rough benchmark, running an ALTER TABLE query on a database table of 10 million records took 28.52 seconds on a mid-range 2023 laptop, though this time will vary depending on the database and hardware being used.

You may wish to change the ClassName column to a varchar type which remove the need to run ALTER TABLE whenever there is a new valid value. Enabling this will result in a trade-off where the size of the database will increase by approximately 7 MB per 100,000 rows.

To enable this, add the following configuration:

CopySilverStripe\ORM\DataObject:
  fixed_fields:
    ClassName: DBClassNameVarchar

SilverStripe\ORM\FieldType\DBPolymorphicForeignKey:
  composite_db:
    Class: "DBClassNameVarchar('SilverStripe\\ORM\\DataObject', ['index' => false])"