#36475: Optimize F-expression slice to MySQL column-prefix index
-------------------------------------+-------------------------------------
Reporter: JaeHyuckSa | Type:
| Cleanup/optimization
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36475>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/010701979c85c61f-a336c718-4efb-45a5-9bae-45b261d6e53d-000000%40eu-central-1.amazonses.com.
