OK, we have now explored some pretty advanced topics in searching for text inside of a database. The actual search technique is pretty rudimentary, just splitting on the search text to get each search term, and then checking if various columns contain those search terms. But we have layered on some real world, advanced requirements too, such as searching the reminders’ titles, notes and tags, and we are computing a count of completed reminders right alongside the search results.

But let’s now improve on the actual search technique being employed here. There is a technology known as “full-text search” that is built into many databases, such as Postgres and MySQL, and of course SQLite has fantastic support for it. Full-text search allows you to maintain a separate table, or really tables, of indexed information about the text in your application so that it can quickly and efficiently search large corpuses of data. It also has some fancy tricks up its sleeve that allow you to rank search results and even highlight the search terms in the results.

Let’s see what it takes to replace our rudimentary substring search technique with a more high-powered full-text search technique.