[Django]-Django MySQL full text search

The previously highest rated answer is deprecated. As of Django 1.10 there is no more search field lookup for MySQL databases (see the search section in the 1.10 documentation).

The release notes for 1.10 also propose a solution to this, by defining a custom lookup:

###__search query lookup

The search lookup, which supports MySQL only and is extremely limited in
features, is deprecated. Replace it with a custom lookup:

from django.db import models

class Search(models.Lookup):
   lookup_name = 'search'

   def as_mysql(self, compiler, connection):
       lhs, lhs_params = self.process_lhs(compiler, connection)
       rhs, rhs_params = self.process_rhs(compiler, connection)
       params = lhs_params + rhs_params
       return 'MATCH (%s) AGAINST (%s IN BOOLEAN MODE)' % (lhs, rhs), params

models.CharField.register_lookup(Search)
models.TextField.register_lookup(Search)

You can use full text search in django

MyItem.objects.filter(title__search="some search text")

One thing is – you can’t define a fulltext index from a Django model, you need to do in directly in a database (using PHPMyAdmin or SQL query)

See Django documentation for field lookup called search

I don’t know if it helps now but I’ve created a new Python Library for Django which supports MySQLs’ and MariaDBs’ native full-text search engine over one or multiple columns:

You can have a look at it on the GitHub repository

There’s also a description how to install it, use it and how to create the FULLTEXT INDEX stuff via Django migrations (Django 1.7+).

If you’ve configured the indexes and set the SearchManager for your model you should be able to run something like:

Mymodel.objects.search('Something')
Mymodel.objects.search('Somet*')
Mymodel.objects.search('+Something -Awesome')

Just wanted to update this topic because I didn’t find an acceptable solution so far and it might help someone out there as well 🙂

Cheers
Domi

If you are looking for a beefy solution I recommend http://haystacksearch.org/

It is very well thought out.

Django provides full-text functionality for PostgreSQL’s only.

From django docs regarding full-text search:

Example:

Entry.objects.filter(headline__search="+Django -jazz Python")

SQL equivalent:

SELECT ... WHERE MATCH(tablename, headline) AGAINST (+Django -jazz Python IN BOOLEAN MODE);

Note this is only available in MySQL and requires direct manipulation of the database to add the full-text index. By default Django uses BOOLEAN MODE for full text searches. See the MySQL documentation for additional details.

Now to the direct manipulation of the database. In MySQL you can create full-text index by following these steps (source article):

• Open command prompt, and enter mysql -u root -p. On prompt enter the root password.
• Enter use your_db_name to switch to your django database.
• Enter CREATE FULLTEXT INDEX index_name ON table_name (column_names).

That’s it! FTS indexing in enabled in your django database. Now you can use the django’s rich QuerySet API to do full-text searches.

Edit: The above quote no longer exists in the django version >1.9.