Andrew Welch
Published , updated · 5 min read · RSS Feed
Please consider 🎗 sponsoring me 🎗 to keep writing articles like this.
Indexing by Custom Field in Craft CMS
Learn how to make a content migration to add database indexes for custom fields in Craft CMS
Craft CMS is an excellent general-purpose content management system, but it can’t automatically optimize for every potential use case.
For example, if you’re doing an Element Query like this:
{% set silverCars = craft.entries()
.section('cars')
.paintColor('silver')
.all()
%}
We’re asking Craft CMS to give us all of the entries in the cars section that have the custom field paintColor equal to silver.
This looks pretty standard… but in this case, paintColor is a custom field that stores its data in a column in the Craft-managed content table of your database.
Unfortunately, there is no database index created automatically for custom fields, so this query won’t scale well as your dataset grows.
In our example, we use the Plain Text field paintColor… but everything works exactly the same with Date fields, Number fields, etc. They are all just columns in the content table in the database.
This article will show you how to create a Content Migration to add a database index for custom fields, which will vastly improve the performance of queries such as the one above.
But first, let’s talk about indexes and why they are important.
Link Indexes in a nutshell
Imagine you have some business cards tossed into a pile on the floor.
If you have just a few business cards, it won’t take long to look through them to find the one you’re looking for.
However, if you have hundreds or even thousands of business cards… it’s going to take a whole lot of time to sift through them all.
So what you can do instead is organize the business cards alphabetically by name, and then searching through them in the future will be much quicker.
This is analogous to how database indexes work: you trade some upfront time & storage space for more efficient querying on things you know you’ll be searching on often.
So why not just index everything?
You might think it’d make sense just to index everything, then. However, this would result in a whole lot of wasted storage space, and could even be less efficient for general day-to-day use.
So it’s best just to index only the things you know you’ll be querying for.
Link Craft CMS Indexes
Craft CMS already creates indexes for many common things you’ll be querying.
However, as noted above, it does not create indexes for custom fields you create. This only comes into play if you’re doing a query based on the value of a custom field.
As in the paintColor field example we previously presented:
{% set silverCars = craft.entries()
.section('cars')
.paintColor('silver')
.all()
%}
It doesn’t create indexes for you because it doesn’t know that you’ll actually be querying based on this custom field, and it would be marvelously inefficient for it to just automatically create an index for every custom field.
N.B.: A separate index is created for searching in Craft CMS. So if all you are doing is a general-purpose search, you’re covered already:
{% set results = craft.entries()
.section('cars')
.search('silver')
.all()
%}
But this will search for silver anywhere in any fields in the cars section, which isn’t always specific enough for what we want to achieve.
N.B.: MySQL can’t create an index for fields that store data in a BLOB/TEXT type, unless a prefix length is specified for it (which Craft CMS currently doesn’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 varchar for Plain Text fields you need indexed:
Advanced settings in the Plain Text field type
For a more in-depth discussion of database indexes, read High Performance MySQL — Chapter 4. Indexes and the article How to Use Indexes to Increase MySQL Database Performance.
Link Content Migrations
Content Migrations are PHP classes that you write which allow you to make one-time changes to the database (or even other things) in a trackable, deterministic manner.
Content Migrations are similar to the migrations that Craft & plugins run when they need to make database changes.
Thankfully, the Craft CLI lets us easily create a new Content Migration. Here we’re creating 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.
Content Migrations you create are stored in the migrations/ directory in your project. Note that it also added a date and timestamp that prefixes the migration name we gave it.
Then after we’re done writing our migration, we can check it into our project’s repository like any other code.
The skeleton migration it created for us doesn’t do anything 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 migration is run. The safeDown() method is optionally implemented, and is called when the migration is reverted.
Let’s fill it in with some code to actually create a database index for our custom paintColor field!
Link Adding a database index for our custom field
Here’s the completed Content 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 directives, public const declaration, and the safeUp() method to the Content Migration you created above to use it. That’s all we changed.
The code iterates through the field handles defined in FIELD_HANDLES, and gets each field.
Then it gets the column name in the content table where that field’s data is stored, and finally just creates an index for it if it doesn’t exist already.
Tada! 🪄
Link Running the content migration
We can run this Content Migration 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 database indexes associated with the content table now, we can see it was added:
Database Index added
Link Wrapping up
If you are performing queries based on custom fields in Craft CMS, the work involved in creating an index will be infinitely worth it, especially if you are working with large datasets.
The potential performance benefits of adding an index to a custom field apply whether you are using Twig, PHP, or GraphQL, because they all resolve to element queries in the end.
The more entries you have, the more gains you’ll see from having an index for custom fields that are used as WHERE clauses in your queries (which is what .myCustomField('someValue') gets translated to).
Happy indexing!