Django MySQL - Setting an index on a Textfield-CodePudding

I have a database of articles that I want to search through. I had been using normal Django ORM to search, which was getting way to slow and then I got to know a little about Indexes in Django. I'm using MySQL and I now know that with MYSQL I cannot put an index field into a TextField as described here in this stack question which I was facing. However in my case I can't change this to CharField.

I was reading through the MyQSL Docs which stated

MySQL cannot index LONGTEXT columns specified without a prefix length on the key part, and prefix lengths are not permitted in functional key parts.

Hence I was of the understanding that since TextField in Django is LONGTEXT for MYSQL, I came across this Django-MySQL package here and thought that using this if I could change the LONGTEXT to a MEDIUMTEXT using this package, this might get resolved. So my updated model I did this

class MyModel(Model):
    ........
    document = SizedTextField(size_class=3)

However, I still see the same error while applying python manage.py makemigrations

django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'document' used in key specification without a key length")

How can I go about resolving this?

CodePudding user response：

All of these related types, TEXT, MEDIUMTEXT, and LONGTEXT, are too large to be indexed without specifying a prefix. An index prefix means that only the first N characters of the string are included in the index. Like this:

create table mytable (
  t text, 
  index myidx (t(200))
);

The prefix length in this example is 200 characters. So only the first 200 characters are included in the index. Usually this is enough to help performance, unless you had a large number of strings that are identical in their first 200 characters.

The longest prefix that MySQL supports depends on the storage engine and the row format. Old versions of MySQL support index prefix up to 768 bytes, which means a lesser number of characters depending on if you use multi-byte character sets like utf8 or utf8mb4. The recent versions of MySQL default to a more modern row format, which supports up to 3072 bytes for an index, again reduced by 3 or 4 bytes per character.

I'm not a regular Django user, so I tried to skim the documentation about defining indexes on model classes. But given a few seconds of reading, I don't see an option to declare a prefix for an index on a long string column.

I think your options are one of the following:

Change the column to a shorter string column that can be indexed
Create the index using the MySQL client, not using Django migrations