Opened 18 hours ago
Last modified 15 hours ago
#35777 new Uncategorized
MySQL: cannot add key limited index on TEXT columns
Reported by: | Tobias Krönke | Owned by: | |
---|---|---|---|
Component: | Migrations | Version: | 5.0 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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!
Change History (5)
follow-up: 2 comment:1 by , 17 hours ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
comment:2 by , 17 hours ago
Replying to Sarah Boyce:
I feel this functionality exists and the way to go about this might be using Index.expressions and 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.
comment:3 by , 17 hours ago
Resolution: | invalid |
---|---|
Status: | closed → new |
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
.
comment:4 by , 15 hours ago
Index.condition with Q(startswith='https://google.com')
is probably also an option.
Update: sorry, that's also documented to not be supported on MySQL
comment:5 by , 15 hours ago
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 = [] condition = None 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)
I feel this functionality exists and the way to go about this might be using Index.expressions and Left