#36475: Optimize F-expression slice to MySQL column-prefix index
-------------------------------------+-------------------------------------
Reporter: JaeHyuckSa | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by JaeHyuckSa:
Old 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.
New 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 Johnson suggested that
Django could handle this directly instead of needing custom index
subclasses.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36475#comment:1>
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/010701979c878dce-48ed3f56-2b51-4056-9c92-00084554a2c6-000000%40eu-central-1.amazonses.com.
