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?
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".)