![php[architect] logo](/wp-content/themes/phpa/images/new_logo_mark_nav.png){kind=logo}
Full-text Search with SQLite
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:
- Ensure full-text search is enabled (see below)
- Create a new virtual full-text search table
- 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.]
Leave a comment
Use the form below to leave a comment:
Responses and Pingbacks
November 14th, 2011 at 3:48 pm
This is awesome. I’m curious how fast full-text searching in SQLite is compared to other options like Lucene, Solr, or full-text search in MySQL. Can it handle hundreds of thousands of records?
November 15th, 2011 at 4:43 pm
I get this error in PHP 5.3.3 on Windows, using Sqlite via PDO extension:
Error: (HY000) (1) near “WITH”: syntax error
Query 1: CREATE VIRTUAL TABLE book WITH fts4 (id, author, title, excerpt)
November 15th, 2011 at 4:45 pm
PDO Driver for SQLite 3.x enabled
SQLite Library 3.6.23.1
November 16th, 2011 at 10:54 pm
[…] the php|architect site there’s a recent tutorial from Jeremy Kendall about full-text searching in SQLite, a lightweight database alternative that, since it’s stored locally, can be used without a […]
November 19th, 2011 at 12:55 am
[…] Orginal Post Click here to cancel reply. […]
December 1st, 2011 at 1:29 pm
Regarding the error that I’ve pasted a few posts earlier.
The solution is to replace keyword “WITH” with “USING”, so it should be:
>CREATE VIRTUAL TABLE book USING fts4 (id, author, title, excerpt)
Pity, that author of this article did not even check his code before publishing.
December 1st, 2011 at 3:07 pm
@Czarek: thanks for pointing that out, and apologies for the error. That was a really bad one on my part. The post has been updated and you’ve been credited with the catch.
February 13th, 2012 at 3:10 pm
Hi,
Can you get this to work with sqlite within a contained html5 / jqtouch phone app, I’m trying to search a full description of an item.
more info can be mailed if required.
September 8th, 2014 at 4:40 pm
HI… I’m trying to replace hard-coded paths in Plex Media Server for Mac OS X (10.9.4) using a similar approach to the one used in this article.
I have hundreds of hard-coded media Libraries I’m created in Plex (I used the Plus symbol to add media via the complete path), so changing these one by one would take forever.
I did a backup of the database “com.plexapp.plugins.library.db” (AFTER closing Plex Media Server of course).
However I lack the SQL knowledge to do the query correctly, but I think I’m close.
I started by creating a Full-text Search (FTS) Table using the following SQL query (the SQL application I used was the latest version of “MesaSQLite” for Mac OS 10.9.4):
CREATE VIRTUAL TABLE utorrent1 USING fts3(content TEXT);
CREATE TABLE utorrent2 (content TEXT);
This worked…
However, I came up with 0 Results when I tried the following two SQL queries:
SELECT * FROM utorrent1 WHERE content MATCH “/Users/zeno/Downloads/uTorrent” ;
SELECT * FROM utorrent2 WHERE content MATCH “/Users/zeno/Downloads/uTorrent” ;
(The only difference is that I queried both “utorrent1” in the first and “utorrent2” in the second).
Any help would be much appreciated!
I just posted this same question to the Plex Pass Members Only support forums, but I don’t know if anyone’s going to answer since this is really just a question about how to formulate the correct SQL query. If you are a Plex Pass Member (i.e., you paid for it), here’s the link to that forum:
https://forums.plex.tv/index.php/topic/120283-plex-media-server-library-path-changes-mac-os-x/#entry716499
I do think having a “Search & Replace” option for people like me who moved a Plex library to a different hard drive and want to update all their paths would be useful to lots of people besides just me, though of course this is not the preferred — or even supported — method according to Plex support 🙂
Thanks everyone!
Regards,
-Michael