Add way to support Column Prefix Key Parts

Description

Heyo!

See my ticket https://code.djangoproject.com/ticket/35777. It seems to me, that django lacks support to create indexes with so-called "Column Prefix Key Parts". These are documented here: https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-column-prefixes and give you a huge performance boost, because these indexes can then be used for istartswith queries on TEXT fields. Would this be something for django itself or should this be rather implemented here? How would you evaluate my workaround from the django ticket?

class ColumnPrefixIndex:
    max_name_length = 60
    contains_expressions = False
    fields_orders = []
    include = []
    expressions = []
    fields = []
    condition = None

    def __init__(self, column, prefix, name):
        self.prefix = prefix
        self.column = column
        self.name = name

    def create_sql(self, model, schema_editor, using="", **kwargs):
        table = model._meta.db_table
        sql = f'CREATE INDEX {self.name} ON {table} ({self.column}({self.prefix}));'
        kwargs['sql'] = sql

        return schema_editor._create_index_sql(
            model,
            name=self.name,
            using=using,
            **kwargs,
        )

    def remove_sql(self, model, schema_editor, **kwargs):
        return schema_editor._delete_index_sql(model, self.name, **kwargs)

    def deconstruct(self):
        kwargs = {'column': self.column, 'prefix': self.prefix, 'name': self.name}
        path = "%s.%s" % (self.__class__.__module__, self.__class__.__name__)
        return path, self.expressions, kwargs

    def clone(self):
        return self.__class__(self.column, self.prefix, self.name)

adamchainz / django-mysql

Add way to support Column Prefix Key Parts #1114

Description