Craft CMS 5 content migration that will create a functional index for custom fields, which are now stored as a JSON column in element_sites.content. Also handles dropping the index if the migration is reverted.

m240515_172552_add_index_to_demo_data.php

<?php
/**
 * Craft CMS 5 content migration that will create a functional index for custom fields, which
 * are now stored as a JSON column in element_sites.content. Also handles dropping the index if
 * the migration is reverted.
 *
 * @licence   MIT
 * @link      https://nystudio107.com
 * @copyright Copyright (c) nystudio107
 */

namespace craft\contentmigrations;

use Craft;
use craft\base\FieldInterface;
use craft\db\Migration;
use craft\db\Table;
use craft\helpers\Console;
use Throwable;
use yii\db\Exception;

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

    /**
     * @inheritdoc
     */
    public function safeUp(): bool
    {
        $entries = Craft::$app->getEntries();
        $entryTypes = $entries->getAllEntryTypes();
        foreach ($entryTypes as $entryType) {
            $customFields = $entryType->getCustomFields();
            foreach ($customFields as $customField) {
                if (in_array($customField->handle, self::FIELD_HANDLES, true)) {
                    $this->addIndexForField($customField);
                }
            }
        }

        return true;
    }

    /**
     * @inheritdoc
     */
    public function safeDown(): bool
    {
        $entries = Craft::$app->getEntries();
        $entryTypes = $entries->getAllEntryTypes();
        foreach ($entryTypes as $entryType) {
            $customFields = $entryType->getCustomFields();
            foreach ($customFields as $customField) {
                if (in_array($customField->handle, self::FIELD_HANDLES, true)) {
                    $this->dropIndexForField($customField);
                }
            }
        }

        return true;
    }

    /**
     * @param FieldInterface $field
     * @throws Exception
     */
    protected function addIndexForField(FieldInterface $field): void
    {
        $db = $this->getDb();
        $tableName = Table::ELEMENTS_SITES;
        $sql = $field->getValueSql();
        $indexName = $this->getIndexNameForField($field);
        $cmd = null;
        // MySQL
        if ($db->getIsMysql()) {
            // If there is no CAST returned (as there wont be for text and other types), add it
            if (!str_starts_with($sql, 'CAST')) {
                $sql = "CAST($sql AS CHAR(255))";
            }
            $cmd = $db->createCommand("ALTER TABLE $tableName ADD INDEX $indexName (( $sql COLLATE utf8mb4_bin )) USING BTREE;");
        }
        // Postgres
        if ($db->getIsPgsql()) {
            $cmd = $db->createCommand("CREATE INDEX $indexName ON $tableName ( $sql );");
        }
        // Execute the command, swallowing any errors
        if ($cmd) {
            try {
                Console::output(Console::ansiFormat("Executing the following SQL to add the index:", [Console::FG_GREEN]));
                Console::output(Console::ansiFormat($cmd->getRawSql(), [Console::FG_YELLOW]));
                $cmd->execute();
            } catch (Throwable $e) {
                // That's fine
            } finally {
                return;
            }
        }
        // If we got to here, we don't support this database type
        throw new Exception("Database type not supported");
    }

    /**
     * @param FieldInterface $field
     * @return void
     */
    protected function dropIndexForField(FieldInterface $field): void
    {
        $indexName = $this->getIndexNameForField($field);
        try {
            $this->dropIndex($indexName, Table::ELEMENTS_SITES);
        } catch (Throwable $e) {
            // That's fine
        }
    }

    /**
     * @param FieldInterface $field
     * @return string
     */
    protected function getIndexNameForField(FieldInterface $field): string
    {
        return $field->handle . '_' . str_replace('-', '_', $field->layoutElement->uid);
    }
}