Hacker News Reader

Comment by noman-land | Hacker News Reader

This is awesome. Maybe you can help me figure out the best way to search. The transcriptions are one row per word since each word is timestamped. I'm currently just doing client side search in JS for the titles and descriptions but I'll have to do proper SQL search for the body of the transcriptions. What's the best way to search for strings of text if the db is one row per word (~4 million rows)?

View on HN

In theory you can add an index on the token text column and then implement full text searching yourself using the index to find the set of documents containing each search term, counting the occurrences per document, and combining the results from all the terms. But FTS5 already does that for you, and it's more efficient than the above.

The traditional FTS5 technique as documented [0] should work, same as if this were a local SQLite database. You'll have to duplicate the transcriptions into TEXT form (one row per document) and then use FTS5 to create a virtual table for full text searching. The TEXT form won't actually be accessed for searching, it's just used to build the inverted index.

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

I don't know enough about how FTS works to help, I'm afraid. I don't know if it's possible to search across multiple rows.

Were I in your position I'd just create a copy of the DB with the full transcript in each row and run the search against that. If you only have 4 million words, creating an extra copy of the database shouldn't be prohibitively large.

Reply on news.ycombinator.com