Issue #3253763: Performance of moderation control state filter is slow

core/modules/content_moderation/src/ContentModerationStateStorageSchema.php

@@ -4,6 +4,7 @@
 
 use Drupal\Core\Entity\ContentEntityTypeInterface;
 use Drupal\Core\Entity\Sql\SqlContentEntityStorageSchema;
+use Drupal\Core\Field\FieldStorageDefinitionInterface;
 
 /**
  * Defines the content moderation state schema handler.
@@ -39,4 +40,22 @@ protected function getEntitySchema(ContentEntityTypeInterface $entity_type, $res
     return $schema;
   }
 
+  /**
+   * {@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();
+
+    if ($table_name == $this->storage->getRevisionDataTable()) {
+      // Add index to moderation state to improve performance for the
+      // views plugins that join using this column.
+      if ($field_name === 'moderation_state') {
+        $this->addSharedTableFieldIndex($storage_definition, $schema);
+      }
+    }
+
+    return $schema;
+  }
+
 }