Optimize F-expression slice to MySQL column-prefix index

[JaeHyuckSa]

Since Django 5.1 you can do this in your model

class MyModel(models.Model):
    field = models.CharField(max_length=200)

    class Meta:
        indexes = [
            Index(F('field')[:10], name='prefix_idx'),
        ]

However, on all backends this still creates:

CREATE INDEX prefix_idx ON myapp_mymodel (SUBSTRING(field, 1, 10));

MySQL supports a more efficient “column prefix” syntax:

CREATE INDEX prefix_idx ON myapp_mymodel (field(10));

This syntax is more efficient and directly supports LIKE and startswith queries on long VARCHAR or TEXT columns.
MySQL docs: ​https://dev.mysql.com/doc/refman/9.3/en/create-index.html#create-index-column-prefixes

This has come up before — see Ticket #35777 — and was also discussed in django-mysql # ​https://github.com/adamchainz/django-mysql/pull/1151#issuecomment-2995608422 where Adam Johnson suggested that Django could handle this directly instead of needing custom index subclasses.

[Sarah Boyce]

Unless I have misunderstood, which is very possible, I don't see how this can be implemented without a new index class like PrefixIndex which was suggested within #35777 and I see is what you have written for django-mysql

Slicing an F expression of a CharField resolves to a Substr expression. The MySQL syntax col_name(length) is (from what I can tell) is index specific and so not we won't be able to override the sql for MySQL within Substr. In the PR discussion you mentioned overriding _create_index_sql but I can't envision what you are thinking. If you have a draft PR to propose this I might be able to see what you mean?

Note that #16460 is almost a duplicate

[Adam Johnson]

The idea is that MySQL could have a custom _create_index_sql() that replaces relevant Substr instances with another expression that then outputs the col_name(length) syntax.

Perhaps this is too complicated, and it's better to make a separate Prefix expression class, though, in Django-MySQL.