Add way to support Column Prefix Key Parts

[tuky]

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]

Hi!

Yeah, we could add such an index class here. It seems to be MySQL/MariaDB specific, so less suitable for Django core, which only has django.contrib.postgres.

Rough plan to get it ready for merging:

1. Add to django_mysql.models.indexes.
2. Add type hints.
3. Add tests. Some of the existing field and operations tests can be used for inspiration. We have 100% coverage here!
4. Add docs - a new page called "indexes" covering it, with the same level of detail as existing docs.
5. Add mention in "exposition" page of docs.

If you have time for this, that would be great!