Difference between Django's __search vs. __icontains

Question

I've started learning Django very recently, so please forgive me if this is too newbie a question for you.

I have a search function implemented in Django. I created in MySQL DB full-text-index and then have a field in my, say, Book model: search_keywords, where I basically store all the words including the title and the content of the book. Then my search function does something similar to this:

search_keyword = self.request.GET.get('keywords')
    if search_keyword:
        # '*' is added for cases where user enters only part of the keyword
        qs = qs.filter(keywords__search=search_keyword + '*')

But the above approach doesn't work if my search query is 'intro' where I know there's a word/term like 'test.introduction' (if the term is just 'introduction' then it works fine).

Then when I try this instead:

        qs = qs.filter(keywords__icontains=search_keyword)

it finds 'test.introduction' etc.

So I begin to wonder why this is the case. Is '__search' only for full-word searches? I know the case for '__icontains' makes sense (case-insensitive and contains part of the word), but why take trouble to create full-text-indexes in the DB just so we can use '__search' in Django? Is that the speed advantage (e.g., in cases of huge volumes of texts to search? Or am I missing something completely here?

Answer 1

The difference is about the resulting SQL Query to be executed on the database... I personally prefer "__icontains" because is supported for all databases, and "__search" only for mysql (as django docs) (also supporting PostgreSQL in Django ≥ 1.10 — see documentation).

Look at the query for each method:

Using __search

>>> str(Contact.objects.filter(first_name__search='john').query)

'SELECT `contact_contact`.`id`, `contact_contact`.`title`, `contact_contact`.`first_name`, `contact_contact`.`last_name`, `contact_contact`.`user_id`, `contact_contact`.`role_id`, `contact_contact`.`organization_id`, `contact_contact`.`dob`, `contact_contact`.`email`, `contact_contact`.`notes`, `contact_contact`.`create_date` FROM `contact_contact` WHERE MATCH (`contact_contact`.`first_name`) AGAINST (john IN BOOLEAN MODE)'

Using __icontains

>>> str(Contact.objects.filter(first_name__icontains='john').query)

'SELECT `contact_contact`.`id`, `contact_contact`.`title`, `contact_contact`.`first_name`, `contact_contact`.`last_name`, `contact_contact`.`user_id`, `contact_contact`.`role_id`, `contact_contact`.`organization_id`, `contact_contact`.`dob`, `contact_contact`.`email`, `contact_contact`.`notes`, `contact_contact`.`create_date` FROM `contact_contact` WHERE `contact_contact`.`first_name` LIKE %john% '