4 ms·
This article doesn't even get into SQLite's full-text search feature, which really is surprisingly good. It's also very widely used - desktop and mobile apps th
by simonw 6y ago
This article doesn't even get into SQLite's full-text search feature, which really is surprisingly good. It's also very widely used - desktop and mobile apps that offer a search feature often build that using SQLite, so it's a very robust and well-trodden path at this point.
I've written a few tools to help work with FTS in SQLite:
- https://sqlite-utils.datasette.io/en/stable/cli.html#configuring-full-text-search https://sqlite-utils.datasette.io/en/stable/cli.html#configu... - command-line utilities for enabling full-text search against an existing SQLite table
- https://sqlite-utils.datasette.io/en/stable/python-api.html#enabling-full-text-search https://sqlite-utils.datasette.io/en/stable/python-api.html#... - that same functionality as a Python library API
- https://docs.datasette.io/en/stable/full_text_search.html https://docs.datasette.io/en/stable/full_text_search.html - Datasette spots full-text search enabled tables and adds a search interface to them
I also put together this article exploring classic search relevance algorithms with SQLite - SQLite FTS5 has relevance built in, but FTS4 leaves it as an exercise for the developer which makes it a really fun tool for understanding how algorithms like BM25 actually work: https://simonwillison.net/2019/Jan/7/exploring-search-relevance-algorithms-sqlite/ https://simonwillison.net/2019/Jan/7/exploring-search-releva...
- raihansaputra 6y agoIs there a way to easily use SQLite FTS on Django?
- simonw 6y agoNot that I've seen. You'd likely have to use a raw SQL query - or the RawSQL class as part of an expression in the ORM.
- slimsag 6y agoSQLite's FTS5 also has experimental support for trigram indexing[0], which is quite nice for having substring matches instead of just whole-token matches with LIKE and GLOB syntax. No support yet for regexp searches over trigram indexes, though, unfortunately (Postgres has this.) I personally think that much more expressive indexed search syntax (more so than just regexp, even) is going to be the future and am actively working on a search engine in Zig for this purpose. [0] https://sqlite.org/fts5.html#the_experimental_trigram_tokenizer https://sqlite.org/fts5.html#the_experimental_trigram_tokeni...
- simonw 6y agoOh cool I didn't know PostgreSQL can use those for regular expressions! I was playing with those indexes for PostgreSQL just this morning: https://til.simonwillison.net/django/enabling-gin-index https://til.simonwillison.net/django/enabling-gin-index
- slimsag 6y agoIf you're using Postgres Trigram indexes at larger scales, I might suggest reading over just the conclusions at the end of my article here: https://devlog.hexops.com/2021/postgres-regex-search-over-10000-github-repositories https://devlog.hexops.com/2021/postgres-regex-search-over-10... e.g. I discuss the fact that pg_tgrm indexes being built does not take advantage of multiple cores, so at large scales you'll benefit from splitting data into multiple tables.
- Uberphallus 6y agoActually pg_tgrm, like all things Postgres generally, is superior. It can rank searches by similarity (e.g. proportion of query trigrams present in the matching column's trigrams) and SQLite doesn't, which is a dealbreaker when dealing with anything that potentially includes typos or mutable word order; in many situations you wanna match (or rank high) 'Johnny Doe' when the query is 'Doe, John', also considering that languages with clear markings of grammatical case rely less on word order (like most Slavic ones) pg_tgrm is a godsend for properly hitting rows with such user input queries.
- anticodon 6y ago> SQLite's full-text search feature Doesn't support languages other than English. At least, that was the case when I last tried it.