Nice question—let’s walk through your options for SQLite3 when you need title search in a basic CRUD app, without overcomplicating things.

Should you use SQLite’s FTS for non-full-text search? #

It depends on your data size and needs.

If you’re dealing with small datasets (say, under 10k rows) and just need basic "contains this keyword" matching, skip FTS entirely. A simple LIKE query is way easier to implement—no extra tables, no setup, just:

SELECT * FROM your_table WHERE title LIKE '%search_term%';

The catch? LIKE does a full table scan, so it’ll get slow once you hit tens of thousands of rows.

If you have larger datasets or need better performance for keyword matches, SQLite’s FTS3/FTS5 is absolutely worth it. It uses inverted indexes to make searches blazingly fast, and it’s not that hard to set up for a single title column. But if you only need simple title matching and don’t want to mess with virtual tables, there’s a middle ground.

Does SQLite have something like PostgreSQL’s trigram support? #

Yes! And it’s perfect for title search where you want flexible partial matches (like catching typos or partial word inputs).

SQLite has a sqlite3_trigram extension (available since version 3.31.0) that adds trigram-based indexing and matching. Here’s how to use it:

1. Load the extension (note: you might need to compile SQLite with it enabled, or load it as a dynamic library):

.load ./sqlite3_trigram

2. Create a trigram index on your title column:
```sql
CREATE TRIGRAM INDEX idx_title_trigram ON your_table(title);

3. Search using MATCH for fuzzy/partial matches:

   SELECT * FROM your_table WHERE title MATCH 'search_term';
   

This works similarly to PostgreSQL’s trigram GIN/GIST indexes—great for short text like titles, where you want to match parts of words or handle minor spelling differences.

Alternatively, if you’re already using FTS5, you can enable its built-in trigram tokenizer for even more flexibility:

CREATE VIRTUAL TABLE your_table_fts USING fts5(title, tokenize='trigram');

Then sync your title data to this virtual table and search with FTS5’s syntax.

Quick decision tree for your app #

• Small dataset, basic exact partial matches: Stick with LIKE—no extra work needed.
• Medium/large dataset, flexible partial/fuzzy matches: Use the trigram extension—lighter than full FTS, better performance than LIKE.
• Need full-text features (multi-field search, ranking, advanced tokenization): Go with SQLite’s FTS3/FTS5.

内容的提问来源于stack exchange，提问作者quadrupleslap