CODEWITHSUNDEEP
sqlitefull-text-search
Qadric
Qadric

Reputation: 13

SQLite FTS necessary?

I am using Python with sqlite3.
Is there an advantage to using FTS3 or FTS4 if I only want to search for words in one column, or I could just use LIKE "%word%"?

Upvotes: 0

Views: 301

Answers (1)

Donal Fellows
Donal Fellows

Reputation: 137567

While yes, you could handle the simplest of cases with LIKE '%word%', FTS allows user queries like clown NOT circus (matches rows talking about clowns but not circuses). It handles stemming (well, in English) so that break would match against breaks but not breakdance. It also builds indexes in a different way so that searching is much faster for this sort of query, and can return not just where the match happened but also a snippet of the matched text.

Finally, all the parsing of those potentially-complex user queries is done for you; that's code you don't have to write at all.

Upvotes: 1

Related Questions