Django 1.8+ and MySQL / MariaDB full-text search

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.

31 Comments

  • Ted

    How does this compare to Postgres full text search with Django, performance-wise?

    • Dominique Barton

      To be honest, I’ve never tested it because MySQL was already set for my project. But in the end, it’s more a “MySQL vs PostgreSQL” than a Django thing. However, a couple of years ago PostgreSQL’s fulltext search was much more mature than the one implemented in MySQL.

  • cookie clicker

    So basically this is all the fuzz about full-text indexes on MySQL and MariaDB….

  • Red

    I had some same question as Ted but you answered it for me too Dom. Thanks dude!

  • www.svgurbana.com

    Well I actually like Django better than anything else.

  • call us

    You would even know that works in Django.

  • cherry u

    This is very helpful! Thanks!

  • CentennialFencing

    No wonder why I can’t use the full text search. Thanks for this short tutorial.

  • towing

    Thanks for this great information you shared.

  • hockey legends

    You’re great, I’ve been looking for this information, it’s not easy to find this information, thank you.

  • bubble pop

    I know how to use Mysql, but I’m still a stranger to some code, I think I need to see some lessons again.

  • happy wheels

    Thanks for your helpful information. I have been struggling to find many questions about this issue. I will follow you!

  • professional house painter

    web based nurturing stage where guardians and guardians to-be can impart their insights, ability and arrangements on all points identified with pregnancy and nurturing. Leyla, who possesses and looks after MHD, constructs her traffic through focusing on explicit watchwords

  • heat and air okc

    I love the assortment of substance on blends it up between useful blog entries about day to day life, what’s happening in her reality and tips for mums and a truly cool digital broadcasts that cover an assortment of subjects to do with nurturing and being a mum. The way that she’s not actually hard selling at all is reviving.

  • bathroom remodel

    The site is beautifully designed and quick to load. It’s a great example of a simple WordPress theme that’s been well customized.

  • cleanlinebuilders.com

    Greetings, in the wake of perusing this amazing piece of composing I’m too happy to even think about sharing my insight here with associates.

  • doula

    God thank you, I find that page!

  • life insurance

    Thank you for inspiring me. I Hooe many more people can read, too. Thank you again! I’ll wait for the next article!

  • Local Citations

    How you guys optimized it? I am new to computer codes and I’m still learning but getting confused with the updates.

  • roofing contractors

    The great thing about this blog is the conversational tone and the real person behind the brand. I think it’s inspiring to other mums to see someone

  • facebook

    diamond engagement rings will be always be the best stuff. it has great style and it is priceless~

  • 토토커뮤니티

    Thank you for this wonderful post! It has long been extremely helpful. I wish that you will carry on posting your knowledge with us.