Is Django db_index helpful in text search queries?

Question

I've the following model in a Django project:

from django.db import models

class Book(models.Model):
    title = models.CharField(verbose_name='Title', max_length=255, db_index=True)
    authors = models.CharField(verbose_name='Authors', max_length=255, db_index=True)
    date = models.DateField(verbose_name='Date', db_index=True)

In the views I need to do a full text search query like the following:

books = Book.objects.filter(Q(title__icontains=query) | Q(authors__icontains=query))

Is the db_index=True actually helping the performances of my query or not?

n0cuous · Accepted Answer · 2022-12-23 09:48:05Z

1

books = Book.objects.filter(Q(title__icontains=query) | Q(authors__icontains=query))

this query does not filter or sort by the indexed fields, so the indexes will not be used. Instead, the database will perform a full text search, which may not be optimized by indexes.

Depending on the size of your Book table and the complexity of your full text search query, you may still see some performance improvements but they will not have a major impact as for now.

If you want a proper full text search query, you may want to consider using a full text search engine such as PostgreSQL's tsvector or Elasticsearch. These tools are specifically designed for fast and efficient full text search.

answered Dec 23, 2022 at 9:48

n0cuous

9210 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dos Over a year ago

Thank you for your answer. "Depending on the size of your Book table" -> hundreds of records; "complexity of your full text search query" -> this is exactly what you see in the queryset filter. Basing on this information, do you see a major impact?

Collectives™ on Stack Overflow

Is Django db_index helpful in text search queries?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related