#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.
--
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 on the web visit
https://groups.google.com/d/msgid/django-updates/010701920fa0121f-d024bb9b-ab8a-4856-9e31-8b44ba5f4274-000000%40eu-central-1.amazonses.com.
