Full-text Search with SQLite

Posted by on November 12, 2011
 

Full-text search with SQLite is so ridiculously easy to implement, there are only two valid reasons you haven’t done it yet. The first is you don’t need the full-text capability, and the second is you didn’t know it could be done.

Here are the steps in full:

  1. Ensure full-text search is enabled (see below)
  2. Create a new virtual full-text search table
  3. Search your full-text table like a boss

(While full-text search is not enabled by default, it was enabled in the pre-compiled dll I installed on my Windows machine.  If full-text search is not enabled (you’ll know if creating a full-text virtual table fails), simply recompile, adding the fts3 switches when you do.)

Create a Full-text Search (FTS) Table

Creating an FTS table is about the same as creating a normal table.  The difference is you’re creating a virtual table specifying the full-text module you’ll be using.

CREATE VIRTUAL TABLE book USING fts4 (id, author, title, excerpt);

No kidding.  That’s all there is to it.  FTS tables can be used (almost) exactly like any other SQLite table, just with liberal amounts of awesome sauce applied.

Search Like a Boss

Now that you’ve created your FTS table, you’re ready to start searching.  The options for search are pretty extensive, including token and token prefix queries,  phrase queries, NEAR queries, and the usual AND, OR, and NOT queries.

All queries against SQLite tables take the form of “<column> MATCH <full-text query expression>” in the WHERE clause of a select statement.  For example, you might want to search the book database for Dickens’ A Tale of Two Cities.  Since you can’t remember the name of the book, but you do remember the line, “It was the best of times, it was the worst of times . . .”, you might try:

SELECT * FROM book WHERE excerpt MATCH 'best NEAR worst';

A phrase search would look like:

SELECT * FROM book WHERE excerpt MATCH '"It was the best of times"';

Those queries are fine for searching the excerpts column, but what if you want to search every column at once?  Perhaps for books by or about Mark Twain or have Mark Twain in the title?

SELECT * FROM book WHERE book MATCH 'Twain';

Pretty slick, eh?  While the query looks incorrect (there is no book column defined in our schema), the book column is a hidden column created and used by fts4, suitable for use in querying all columns of the book table.

Parting Thoughts

Using full-text search with SQLite is a breeze.  It’s easy to get up and running and lightning fast to use.  While you can treat an FTS table almost exactly like a regular SQLite table, there are some important differences that you need to be aware of.  If you’d like to dig deeper into SQLite’s full-text features, the documentation is excellent, concise, and well worth the 15 or so minutes it takes to read through it.

[UPDATE: Edited to correct the SQLite syntax error pointed out by Czarek.]


About the author—Jeremy is a PHP developer, a Zend Framework expert, the organizer of Memphis PHP, a public speaker, an open source contributor, and an amateur photographer. He currently resides in his hometown of Memphis, TN.