Andrew Welch · Insights · #database #craftcms #migration

Published , updated · 5 min read ·


Please consider 🎗 sponsoring me 🎗 to keep writing articles like this.

Indexing by Custom Field in Craft CMS

Learn how to make a con­tent migra­tion to add data­base index­es for cus­tom fields in Craft CMS

Indexing by custom fields craft cms

Craft CMS is an excel­lent gen­er­al-pur­pose con­tent man­age­ment sys­tem, but it can’t auto­mat­i­cal­ly opti­mize for every poten­tial use case.

For exam­ple, if you’re doing an Ele­ment Query like this:

{% set silverCars = craft.entries()
  .section('cars')
  .paintColor('silver')
  .all()
%}

We’re ask­ing Craft CMS to give us all of the entries in the cars sec­tion that have the cus­tom field paintColor equal to silver.

This looks pret­ty stan­dard… but in this case, paintColor is a cus­tom field that stores its data in a col­umn in the Craft-man­aged content table of your database.

Unfor­tu­nate­ly, there is no data­base index cre­at­ed auto­mat­i­cal­ly for cus­tom fields, so this query won’t scale well as your dataset grows.

In our exam­ple, we use the Plain Text field paintColor… but every­thing works exact­ly the same with Date fields, Num­ber fields, etc. They are all just columns in the content table in the database.

This arti­cle will show you how to cre­ate a Con­tent Migra­tion to add a data­base index for cus­tom fields, which will vast­ly improve the per­for­mance of queries such as the one above.

But first, let’s talk about index­es and why they are important.

Link Indexes in a nutshell

Imag­ine you have some busi­ness cards tossed into a pile on the floor.

Pile of business cards

If you have just a few busi­ness cards, it won’t take long to look through them to find the one you’re look­ing for.

How­ev­er, if you have hun­dreds or even thou­sands of busi­ness cards… it’s going to take a whole lot of time to sift through them all.

So what you can do instead is orga­nize the busi­ness cards alpha­bet­i­cal­ly by name, and then search­ing through them in the future will be much quicker.

This is anal­o­gous to how data­base index­es work: you trade some upfront time & stor­age space for more effi­cient query­ing on things you know you’ll be search­ing on often.

So why not just index everything?

You might think it’d make sense just to index every­thing, then. How­ev­er, this would result in a whole lot of wast­ed stor­age space, and could even be less effi­cient for gen­er­al day-to-day use.

So it’s best just to index only the things you know you’ll be query­ing for.

Link Craft CMS Indexes

Craft CMS already cre­ates index­es for many com­mon things you’ll be querying.

How­ev­er, as not­ed above, it does not cre­ate index­es for cus­tom fields you cre­ate. This only comes into play if you’re doing a query based on the val­ue of a cus­tom field.

As in the paintColor field exam­ple we pre­vi­ous­ly presented:

{% set silverCars = craft.entries()
  .section('cars')
  .paintColor('silver')
  .all()
%}

It does­n’t cre­ate index­es for you because it does­n’t know that you’ll actu­al­ly be query­ing based on this cus­tom field, and it would be mar­velous­ly inef­fi­cient for it to just auto­mat­i­cal­ly cre­ate an index for every cus­tom field.

N.B.: A sep­a­rate index is cre­at­ed for search­ing in Craft CMS. So if all you are doing is a gen­er­al-pur­pose search, you’re cov­ered already:

{% set results = craft.entries()
  .section('cars')
  .search('silver')
  .all()
%}

But this will search for silver any­where in any fields in the cars sec­tion, which isn’t always spe­cif­ic enough for what we want to achieve.

N.B.: MySQL can’t cre­ate an index for fields that store data in a BLOB/TEXT type, unless a pre­fix length is spec­i­fied for it (which Craft CMS cur­rent­ly does­n’t do). Most fields in Craft CMS do not use the BLOB/TEXT type by default, but Plain Text field types do. So change the type to var­char for Plain Text fields you need indexed:

Text field varchar

Advanced set­tings in the Plain Text field type

For a more in-depth dis­cus­sion of data­base index­es, read High Per­for­mance MySQL — Chap­ter 4. Index­es and the arti­cle How to Use Index­es to Increase MySQL Data­base Per­for­mance.

Link Content Migrations

Con­tent Migra­tions are PHP class­es that you write which allow you to make one-time changes to the data­base (or even oth­er things) in a track­able, deter­min­is­tic manner.

Con­tent Migra­tions are sim­i­lar to the migra­tions that Craft & plu­g­ins run when they need to make data­base changes.

Thank­ful­ly, the Craft CLI lets us eas­i­ly cre­ate a new Con­tent Migra­tion. Here we’re cre­at­ing one named add_index_for_paintColor_fieldusing the php craft migrate/create command:

/var/www/project/cms_v4 $ php craft migrate/create add_index_for_paintColor_field
Create new migration '/var/www/project/cms_v4/migrations/m230629_004656_add_index_for_paintColor_field.php'? (yes|no) [yes]:
New migration created successfully.

Con­tent Migra­tions you cre­ate are stored in the migrations/ direc­to­ry in your project. Note that it also added a date and time­stamp that pre­fix­es the migra­tion name we gave it.

Then after we’re done writ­ing our migra­tion, we can check it into our pro­jec­t’s repos­i­to­ry like any oth­er code.

The skele­ton migra­tion it cre­at­ed for us does­n’t do any­thing at the moment:

<?php

namespace craft\contentmigrations;

use Craft;
use craft\db\Migration;

/**
 * m230629_004656_add_index_for_paintColor_field migration.
 */
class m230629_004656_add_index_for_paintColor_field extends Migration
{
    /**
     * @inheritdoc
     */
    public function safeUp(): bool
    {
        // Place migration code here...

        return true;
    }

    /**
     * @inheritdoc
     */
    public function safeDown(): bool
    {
        echo "m230629_004656_add_index_for_paintColor_field cannot be reverted.\n";
        return false;
    }
}

The safeUp() method is called when the migra­tion is run. The safeDown() method is option­al­ly imple­ment­ed, and is called when the migra­tion is reverted.

Let’s fill it in with some code to actu­al­ly cre­ate a data­base index for our cus­tom paintColor field!

Link Adding a database index for our custom field

Here’s the com­plet­ed Con­tent Migration:

<?php

namespace craft\contentmigrations;

use Craft;
use craft\db\Migration;
use craft\db\Table;
use craft\helpers\ElementHelper;

/**
 * m230629_004656_add_index_for_paintColor_field migration.
 */
class m230629_004656_add_index_for_paintColor_field extends Migration
{
    // The field handles we want to add database indexes for
    public const FIELD_HANDLES = [
        'paintColor'
    ];

    /**
     * @inheritdoc
     */
    public function safeUp(): bool
    {
        $fields = Craft::$app->getFields();
        // Iterate through all of our fields by handle
        foreach (self::FIELD_HANDLES as $fieldHandle) {
            $field = $fields->getFieldByHandle($fieldHandle);
            if ($field) {
                // Find the column name the field uses in the content table
                $column = ElementHelper::fieldColumnFromField($field);
                if ($column) {
                    // Create an index for this field if it isn't already created
                    $this->createIndexIfMissing(Table::CONTENT, [$column], false);
                }
            }
        }
        return true;
    }

    /**
     * @inheritdoc
     */
    public function safeDown(): bool
    {
        echo "m230629_004656_add_index_for_paintColor_field cannot be reverted.\n";
        return false;
    }
}

You can copy the use direc­tives, public const dec­la­ra­tion, and the safeUp() method to the Con­tent Migra­tion you cre­at­ed above to use it. That’s all we changed.

The code iter­ates through the field han­dles defined in FIELD_HANDLES, and gets each field.

Then it gets the col­umn name in the content table where that field­’s data is stored, and final­ly just cre­ates an index for it if it does­n’t exist already.

Tada! 🪄

Link Running the content migration

We can run this Con­tent Migra­tion to test it using the php craft migrate/all command:

/var/www/project/cms_v4 $ php craft migrate/all
Checking for pending migrations ...
Total 1 new content migration to be applied:
    - m230629_004656_add_index_for_paintColor_field

Apply the above migration? (yes|no) [no]:yes
Create database backup? (yes|no) [no]:no
Skipping database backup.
*** applying m230629_004656_add_index_for_paintColor_field
    > create index idx_pgrmitkqzbwgnxotqehrsncnrwjpmcgpusxk on {{%content}} (field_paintColor) ... done (time: 0.026s)
*** applied m230629_004656_add_index_for_paintColor_field (time: 0.072s)


1 migration was applied.

Migrated up successfully.

And indeed if we look at the data­base index­es asso­ci­at­ed with the con­tent table now, we can see it was added:

Database indexes added

Data­base Index added

Link Wrapping up

If you are per­form­ing queries based on cus­tom fields in Craft CMS, the work involved in cre­at­ing an index will be infi­nite­ly worth it, espe­cial­ly if you are work­ing with large datasets.

The poten­tial per­for­mance ben­e­fits of adding an index to a cus­tom field apply whether you are using Twig, PHP, or GraphQL, because they all resolve to ele­ment queries in the end.

The more entries you have, the more gains you’ll see from hav­ing an index for cus­tom fields that are used as WHERE claus­es in your queries (which is what .myCustomField('someValue') gets trans­lat­ed to).

Hap­py indexing!

${ category } · ${ blog.postDate }

${ blog.title }

#${ tag.title }