Implementing Full-Text Search

1. Creating Full-Text Index

@@fulltext([title, body])
DBDetail
MySQLNative FULLTEXT
PGPreview fullTextSearchPostgres, uses tsvector

2. Using Search Filter

await prisma.post.findMany({
  where: { title: { search: "react & typescript" } }
});
OperatorSyntax
AND&
OR|
NOT!
Prefixword:*

3. Ranking Search Results

await prisma.post.findMany({
  where: { title: { search: "prisma" } },
  orderBy: { _relevance: { fields: ["title"], search: "prisma", sort: "desc" } }
});
AspectDetail
_relevanceComputed score
Multi-fieldWeight by field listing order

4. Searching Multiple Fields

ApproachDetail
Composite fulltext@@fulltext([title, body])
OR clausesSearch each field separately

5. Using Natural Search Mode

MySQL ModeDetail
NATURAL LANGUAGEDefault; ranks by relevance
BOOLEANOperator-based
WITH QUERY EXPANSIONPseudo-relevance feedback

6. Implementing Autocomplete Pattern

StrategyDetail
TrigramPG pg_trgm + GIN index
Prefix indexname LIKE 'abc%' on B-tree
ExternalMeilisearch / Algolia / Typesense

7. Escaping Special Characters

CharHandling
& | !Reserved operators — escape with quotes
User inputSanitize before passing to search

8. Using Stemming Configuration

DBDetail
PGConfigurable tsvector language
MySQLBuilt-in stopwords; no stemming

9. Combining Search with Filters

await prisma.post.findMany({
  where: {
    AND: [
      { body: { search: "performance" } },
      { published: true }
    ]
  }
});
TipDetail
Index orderFilter columns first, then search

10. Optimizing Search Performance

TipDetail
Materialize tsvectorStore column + GIN index
Limit fieldsIndex only searched fields
External engineUse for large catalogs