[Django] #35777: MySQL: cannot add key limited index on TEXT columns

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+-----------------------------------------
Reporter: Tobias Krönke | Type: Uncategorized
Status: new | Component: Migrations
Version: 5.0 | 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
-------------------------------+-----------------------------------------
Heyo!

I have a model with a text field `url = models.TextField()` and I would
like to add an index to my MySQL 8.0.36 DB to speed up queries like
`url__startswith='https://google.com%'` -> `WHERE url LIKE
'https://google.com%'`. I can actually do it by creating an index manually
like so:

{{{
CREATE INDEX `my_limited_prefix_index` ON `my_model_table` (url(100));
}}}

However, I would like to be able to do it with the ORM. Having it only in
the migrations as a data migration has many downsides:

* cannot run tests without running the migrations if i need the index in
tests
* makes squashing migrations more painful

But it seems, this is impossible to achieve with the `Meta.indexes`:


{{{
Index(fields=['url(100)'], name='my_limited_prefix_index')
}}}
-> denied by django with `(models.E012) 'indexes' refers to the
nonexistent field 'url(100)'`


{{{
Index(expressions.RawSQL('url(100)', ()), name='my_limited_prefix_index')
}}}
-> denied by MySQL with syntax error (see
https://github.com/sqlalchemy/sqlalchemy/issues/5462, django adds double
parantheses which is only valid syntax for expressions, but here I need a
column definition)


{{{
Index(expressions.RawSQL('LEFT(url,100)', ()),
name='my_limited_prefix_index')
}}}
-> creates a useless index that cannot be used by MySQL for `startswith`
(aka not sargable anymore).

I guess my favourite solution would be to allow the 2nd way with being
able to turn off the surrounding expression parantheses.


Thx!
--
Ticket URL: <https://code.djangoproject.com/ticket/35777>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+--------------------------------------

Reporter: Tobias Krönke | Owner: (none)
Type: Uncategorized | Status: closed
Component: Migrations | Version: 5.0
Severity: Normal | Resolution: invalid

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: => invalid
* status: new => closed

Comment:

I feel this functionality exists and the way to go about this might be
using
[https://docs.djangoproject.com/en/5.0/ref/models/indexes/#django.db.models.Index.expressions
Index.expressions] and [https://docs.djangoproject.com/en/5.0/ref/models
/database-functions/#left Left]
--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:1>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+--------------------------------------
Reporter: Tobias Krönke | Owner: (none)
Type: Uncategorized | Status: closed
Component: Migrations | Version: 5.0
Severity: Normal | Resolution: invalid
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 Tobias Krönke):

Replying to [comment:1 Sarah Boyce]:

> I feel this functionality exists and the way to go about this might be
using
[https://docs.djangoproject.com/en/5.0/ref/models/indexes/#django.db.models.Index.expressions
Index.expressions] and [https://docs.djangoproject.com/en/5.0/ref/models
/database-functions/#left Left]

Thx. Unfortunately, your feeling is not accurate. Your suggestion results
in the same index created as my 3rd approach in the OP. That index however
is useless. It cannot be used by MySQL for speeding up `startswith`
queries.
--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:2>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+--------------------------------------
Reporter: Tobias Krönke | Owner: (none)

Type: Uncategorized | Status: new

Component: Migrations | Version: 5.0
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 Tobias Krönke):

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

Comment:

This is the kind of index I would like to be able to create:
https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-
column-prefixes. So called `Column Prefix Key Parts`.
--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:3>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+--------------------------------------
Reporter: Tobias Krönke | Owner: (none)
Type: Uncategorized | Status: new
Component: Migrations | Version: 5.0
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
-------------------------------+--------------------------------------

Comment (by Sarah Boyce):

[https://docs.djangoproject.com/en/5.0/ref/models/indexes/#condition
Index.condition] with `Q(startswith='https://google.com')` is probably
also an option.
--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:4>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+--------------------------------------
Reporter: Tobias Krönke | Owner: (none)
Type: Uncategorized | Status: new
Component: Migrations | Version: 5.0
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
-------------------------------+--------------------------------------

Comment (by Tobias Krönke):

The `url__startswith='https://google.com%'` query was just an example. I
would like to optimize all `startswith` queries on that field. I've
created this crude workaround and implemented my own `Index`:


{{{
class ColumnPrefixIndex:
max_name_length = 60
contains_expressions = False
fields_orders = []
include = []
expressions = []
fields = []

def __init__(self, column, prefix, name):
self.prefix = prefix
self.column = column
self.name = name

def create_sql(self, model, schema_editor, using="", **kwargs):
table = model._meta.db_table
sql = f'CREATE INDEX {self.name} ON {table}
({self.column}({self.prefix}));'
kwargs['sql'] = sql

return schema_editor._create_index_sql(
model,
name=self.name,
using=using,
**kwargs,
)

def deconstruct(self):
kwargs = {'column': self.column, 'prefix': self.prefix, 'name':
self.name}
path = "%s.%s" % (self.__class__.__module__,
self.__class__.__name__)
return path, self.expressions, kwargs

def clone(self):
return self.__class__(self.column, self.prefix, self.name)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:5>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns

-------------------------------+------------------------------------------
Reporter: Tobias Krönke | Owner: ishaan-sangwan
Type: Uncategorized | Status: assigned

Component: Migrations | Version: 5.0
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 ishaan-sangwan):

* owner: (none) => ishaan-sangwan
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:6>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns
-------------------------------+------------------------------------------
Reporter: Tobias Krönke | Owner: ishaan-sangwan
Type: Uncategorized | Status: assigned
Component: Migrations | Version: 5.0
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+------------------------------------------
Changes (by ishaan-sangwan):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:7>

[Django]

#35777: MySQL: cannot add key limited index on TEXT columns

-------------------------------------+-------------------------------------
Reporter: Tobias Krönke | Owner: ishaan-
| sangwan
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: wontfix
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 Natalia Bidart):

* component: Migrations => Database layer (models, ORM)
* resolution: => wontfix
* stage: Accepted => Unreviewed
* status: assigned => closed
* type: Uncategorized => New feature
* version: 5.0 =>

Comment:

Hello ishaan-sangwan and Tobias!

Thank you for your interest in making Django better. I'm afraid we can't
accept this ticket yet, we may need to gather some consensus from the
community before going ahead and adding this new index. To me, this feels
a niche use case and thus I don't think this applies to the broader
ecosystem, and Django is a framework designed to offer robust and accurate
solutions for common scenarios.

If we would add a new index class, we should consider supporting all the
DB backends that Django supports, not just MySQL. If we can't support all
backends, I'm not sure it' a good idea to add this to Django. From a quick
search, I'm not sure if this is possible?

To fully evaluate this, the recommended path forward is to first propose
and discuss the idea with the community and gain consensus. To do that,
please consider starting a new conversation on the
[https://forum.djangoproject.com/c/internals/5 Django Forum], where you'll
reach a broader audience and receive additional feedback. Please be sure
to add details about how this new index would look like for the other DB
backends.

I'll close the ticket for now, but if the community agrees with the
proposal, please return to this ticket and reference the forum discussion
so we can re-open it. For more information, please refer to
[https://docs.djangoproject.com/en/stable/internals/contributing/bugs-and-
features/#requesting-features the documented guidelines for requesting
features].
--
Ticket URL: <https://code.djangoproject.com/ticket/35777#comment:8>