Are you still using SQL ‘LIKE’?

Suat ATAN (Ph.D.)
2 min readApr 18, 2022

The only way veteran software developer knows about the searching in the textual field is in the SQL LIKE operator. It is old-fashioned and slow for large databases. Are you still use it?

Think about another additional database table that indexes every single word within a text field of our original table with the original table id. As if thesaurus is on the last page of the book.

Word, Page(s)

So what will the table be for? Same with the thesaurus. We have a database table to access the page (IDs) instead of the reviewing entire book. Isn’t good? It would be quicker than conventional searching with SQL LIKE.

You don’t need to build this table manually. ElasticSearch, Solr, and Whoosh are available and simply perform this task.

Instead of explaining with long instructions let me tell it with short notes and you can find details.

This instruction is for Django and Whoosh. Whoosh does not require additional installation like ElasticSearch.

1- Install Django Haystack Package: This package is a universal package for connecting Django and the Indexing tool (for this example Whoosh). https://django-haystack.readthedocs.io/en/v2.4.1/tutorial.html

2- Write indexer code (it is very standard) like below (in the same directory with your models)

import datetime
from haystack import indexes
from myapp.models import Note
class YourTableNameIndex(indexes.SearchIndex, indexes.Indexable): text = indexes.CharField(document=True, use_template=True)#do not change the name 'text' and other fields you can change 'model_attr' with your original table columns
author = indexes.CharField(model_attr='user')
pub_date = indexes.DateTimeField(model_attr='pub_date')

def get_model(self):
return Note

def index_queryset(self, using=None):
"""Used when the entire index for model is updated."""
return self.get_model().objects.filter(pub_date__lte=datetime.datetime.now())

3- Run python manage.py update_index and create your thesaurus. You will see a new directory that includes your indexes.

4-Write ViewSet to access Whoosh and your indexed result like this tutorial : https://aaravtech.medium.com/improve-search-api-using-django-haystack-and-whoosh-20b3aa562112

You will be able to access your index like this: http://127.0.0.1:8000/products/search/

--

--

Suat ATAN (Ph.D.)

Data Scientist @VectorSolv Quebec - Canada. Podcaster. Author