id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35777	MySQL: cannot add key limited index on TEXT columns	Tobias Krönke	ishaan sangwan	"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!"	New feature	closed	Database layer (models, ORM)		Normal	wontfix			Unreviewed	0	0	0	0	0	0