Drupal - How to add a database index to a base field

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;
  }
}

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).


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.