Opened 3 months ago

Last modified 3 months ago

#36475 closed Cleanup/optimization

Optimize F-expression slice to MySQL column-prefix index — at Initial Version

Reported by: JaeHyuckSa Owned by:
Component: Database layer (models, ORM) Version: 5.1
Severity: Normal Keywords:
Cc: Adam Johnson Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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 suggested that Django could handle this directly instead of needing custom index subclasses.

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top