mysqldjangodjango-mysql

Django MySQL - Setting an index on a Textfield


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?


Solution

  • returning all the articles that contain a given word passed by the client. So would be something SELECT * from articles WHERE text CONTAINS searchword

    Add

    FULLTEXT(text)
    

    and use

    WHERE MATCH(text) AGAINST("searchword")
    

    or perhaps

    WHERE MATCH(text) AGAINST("+searchword" IN BOOLEAN MODE)
    

    It will run very fast. There are caveats -- short words and "stop" words (like "the") are ignored.

    (If DJango cannot facilitate that, then you have to do it with "raw SQL".)