[Django] #36475: Optimize F-expression slice to MySQL column-prefix index

[Django]

#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.

[Django]

#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]

#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
-------------------------------------+-------------------------------------

Changes (by Adam Johnson):

* cc: Adam Johnson (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/36475#comment:2>

[Django]

#36475: Optimize F-expression slice to MySQL column-prefix index
-------------------------------------+-------------------------------------
Reporter: JaeHyuckSa | Owner: (none)

Type: | Status: closed

Cleanup/optimization |
Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: needsinfo

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => needsinfo
* status: new => closed

Comment:

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
--
Ticket URL: <https://code.djangoproject.com/ticket/36475#comment:3>

[Django]

#36475: Optimize F-expression slice to MySQL column-prefix index
-------------------------------------+-------------------------------------
Reporter: JaeHyuckSa | Owner: (none)
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36475#comment:4>