How to add a database index to a base field

Question

Goal:

Add a database index to a 'created' field.

Given:

There is a custom entity with a base field created. The field is stored the entity table. Field source code of the created field:

$fields['created'] = BaseFieldDefinition::create('created')
  ->setLabel(t('Created'))
  ->setDescription(t('The time that the entity was created.'))
  ->setDisplayOptions('view', [
    'label' => 'hidden',
    'type' => 'timestamp',
    'weight' => 3,
    'settings' => [
      'date_format' => 'medium',
      'custom_date_format' => '',
      'timezone' => '',
    ],
  ])
  ->setDisplayConfigurable('view', TRUE);

What I researched:

I have not found a way to add index using the \Drupal\Core\Field\BaseFieldDefinition().

As I understand indexes are defined in a @FieldType plugin. The created field is implemented via the \Drupal\Core\Field\Plugin\Field\FieldType\TimestampItem() plugin and obviously does not have have indexes there.

P.S. I know, that I can write an updatescript to add an index to the table manually.

Answer 1

Currently the only way to do that is to create your own storage_schema entity handler.

See Node as an example, the annotation:

*     "storage_schema" = "Drupal\node\NodeStorageSchema",

There you can alter both the schema for a while table, or you can do it for a specific field. NodeStorageSchema does both:

  /**
   * {@inheritdoc}
   */
  protected function getEntitySchema(ContentEntityTypeInterface $entity_type, $reset = FALSE) {
    $schema = parent::getEntitySchema($entity_type, $reset);

    $schema['node_field_data']['indexes'] += array(
      'node__frontpage' => array('promote', 'status', 'sticky', 'created'),
      'node__status_type' => array('status', 'type', 'nid'),
      'node__title_type' => array('title', array('type', 4)),
    );

    return $schema;
  }

And below in getSharedTableFieldSchema():

if ($table_name == 'node_revision') {
  switch ($field_name) {
    case 'langcode':
      $this->addSharedTableFieldIndex($storage_definition, $schema, TRUE);
      break;

    case 'revision_uid':
      $this->addSharedTableFieldForeignKey($storage_definition, $schema, 'users', 'uid');
      break;
  }
}

Answer 2

For those wondering which part from berdir's reply is necessary for creating an index on a base column - here is a full example on creating index on a string column sku:

ekr_product/Entity/Product.php:

<?php

 * @ContentEntityType(
...
 *     "storage_schema" = "Drupal\ekr_product\ProductStorageSchema",

ekr_product/ProductStorageSchema.php:

<?php

namespace Drupal\ekr_product;

use Drupal\Core\Entity\Sql\SqlContentEntityStorageSchema;
use Drupal\Core\Field\FieldStorageDefinitionInterface;

/**
 * Defines the node schema handler.
 */
class ProductStorageSchema extends SqlContentEntityStorageSchema {

  /**
   * {@inheritdoc}
   */
  protected function getSharedTableFieldSchema(FieldStorageDefinitionInterface $storage_definition, $table_name, array $column_mapping) {
    $schema = parent::getSharedTableFieldSchema($storage_definition, $table_name, $column_mapping);
    $field_name = $storage_definition->getName();

    switch ($field_name) {
      case 'sku':
        $this->addSharedTableFieldIndex($storage_definition, $schema, TRUE);
        break;
    }

    return $schema;
  }

}

Few comments on this.

You might be thinking why using method getSharedTableFieldSchema when creating index on the base table? Truly, shared word is sorta associated with fields stored in a separate tables. But from the function's documentation it follows that if your schema modification isn't cross-field you should use exactly this function.

Note also that the third parameter to addSharedTableFieldIndex is set to TRUE. Suddenly this makes the column you're creating index on NOT NULL. This is the same as if you'd be doing:

$schema['fields']['your_field']['not null'] = TRUE;

somewhere in schema overrides. This is a way to make the column required at the database level.

HTH.

Answer 3

Besides the options berdir posted, configurable fields can have manually added indexes via \Drupal\field\Entity\FieldStorageConfig::$indexes

For example, in field.storage.media.field_media_video_embed_field.yml file you'd have:

indexes:
  field_media_video_embed_field__value:
    - value

to add an index on the value property. Note the second line in this example is just the index name and has no special meaning and could be anything else, it's just a naming convention. But the third line here needs to be the property name and not the SQL database column name (which would be field_media_video_embed_field__value).

Answer 4

While the above API approaches are recommended, here's one more raw approach that can be useful for some cases.

In this case, it adds an SQL index to a base field for the MySQL/MariaDB database engine:

/**
 * Add SQL index on published_date base field
 */
function MY_MODULE_update_8010(&$sandbox) {
  // Add index
  // ALTER TABLE $table ADD INDEX $name ($column1, $column2..)
  db_query("ALTER TABLE node_field_data ADD INDEX node_field__published_date (published_date)");
}