If you want to use MySQL’s or MariaDB’s full-text search feature with Django then you might have a problem, because Django doesn’t support MySQL’s full-text search out of the box. You can either do that by performing raw SQL queries or you extend the model’s SearchManager.

Solr and Haystack

If you’re looking forward to a search-intensive Django application then you might be better with a 3rd-party solution like Apache Solr. Apache Solr is Lucene-based search platform, which describes itself as:

Solr is the popular, blazing-fast, open source enterprise search platform built on Apache Lucene^™.

Fortunately there are other Django users out there who had the same requirements and they’ve built a project called Haystack. Haystack provides modular search for Django with an API which allows you to use different backends (e.g. Solr). The nice thing about Haystack is that you don’t need to modify your source code to support it. Just head over to their tutorial and get started with it.

MySQL / MariaDB full-text search

I don’t dig deeper into Haystack in this post. However, I’ll show you how to configure and setup your Django project so it supports the native full-text search engine of MySQL / MariaDB.

How does full-text search works?

With the latest versions of MySQL and MariaDB you should have full-text search support for the MyISAM, InnoDB and Mroonga engines. Because everything related to full-text search is already really well documented in the MySQL or MariaDB docs, I’ll try to keep it simple.

Let’s say you’ve a customer table and you want to have two columns (i.e. first_name and last_name) to be full-text searchable. First of all, you’ve to create an index for these columns:

CREATE FULLTEXT INDEX customer_name_index ON customer (first_name, last_name);

Now you should be able to run a full-text search based on these two columns:

SELECT * FROM customer WHERE MATCH(first_name, last_name) AGAINST('Domi*' IN BOOLEAN MODE);

As you can see, this should search for the term Domi* in the columns first_name and last_name. Because we’re using BOOLEAN MODE in our search query we can use the wildcard * expression. So basically this is all the fuzz about full-text indexes on MySQL and MariaDB.

Let’s see how we can use that in Django.

Creating the index with Django migrations

Because we don’t want to create the indexes manually, we’ve to do them via Django migrations.
Let’s create an empty migration for our customer app:

./manage.py makemigrations --empty customer

Now open the created migration file and add our SQL migrations:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.db import migrations, models

class Migration(migrations.Migration):

    dependencies = [
        ('customer', '0002_auto_20160118_2254'),
    ]

    operations = [
        migrations.RunSQL(
            ('CREATE FULLTEXT INDEX customer_name_index ON customer_customer (first_name, last_name)',),
            ('DROP INDEX customer_name_index on customer_customer',)
        ),
    ]

This should be enough to create (or reverse) the full-text search index. Run the migrations to see if it’s working:

./manage.py migrate

SearchManager

I’ve created a new SearchManager which supports the MySQL / MariaDB full-text search feature. It’s hosted here on GitHub.

As we now have the database index, we still need to tell Django that it should use this index. This is really straightforward as we can overwrite the model’s objects attribute with our own SearchManager:

from myproject.fulltext import SearchManager

class Customer(models.Model):
    ''' Customer model. '''

    # Enable full-text search support for first_name and last_name fields.
    objects    = SearchManager(['first_name', 'last_name'])

    first_name = models.CharField(max_length=32)
    last_name  = models.CharField(max_length=32)
    # more fields...

This will import and define the fulltext.SearchManager as default search manager for our Customer model. As you can see above, the search manager is created with a default list of searchable fields. These fields are optional and you can omit them if you want to specify them later in the search query itself.

Search

You should now be able to use the full-text search by using the new search() method implemented by the fulltext.SearchManager:

# Search default fields defined in SearchManager().
Customer.objects.search('Domi*')

# Search specific fields.
Customer.objects.search('Domi*', ['first_name', 'last_name'])

IMPORTANT: Please remember you’ve to create a full-text index for the defined fields before you can search them.