Hacker News Reader

Comment by jcuenod | Hacker News Reader

Huh, just yesterday I blogged[0] about using FTS in SQLite[1] to search my PDF database. SQLite's full-text search is really excellent. The thing that tripped me up for a while was `GROUP BY` with the `snippet`/`highlight` function but that's the point of the blog post.

[0] https://jcuenod.github.io/bibletech/2021/07/26/full-text-sea...

[1] https://www.sqlite.org/fts5.html

View on HN

We've been using SQLite's FTS capabilities to index customer log files since 2017 or so. It's been a wonderful approach for us. Even if we move to our own in-house data store (event sourced log), we would still continue using SQLite for tracing because it brings so many of these sorts of benefits.

A very well written article about SQLite FTS5! One question - it seems that your search result displays the matching paging number, how did you do that? because as far as I know, unlike FTS4, FTS5 has no `offsets` function.

I index each page individually. It's not in the article (because I also don't explain separating out the metadata) but I did weigh the options. I considered indexing page pairs, for example (and having every page indexed twice). But I figured that if search terms are broken across pages, they're likely to also be separated by text in headers & footers, page numbers, and footnotes. So in the end I decided to just index individual pages. The FTS table now has `id`, `page_number`, and `content` columns (where `id` is the foreign key to a table that stores the metadata).

Reply on news.ycombinator.com