Implement plein-texte search en SQL
Plein-texte search indexes mots en texte pour rapide searching. Apprendre en utiliser FULLTEXT indexes, tsvector, et ranking.
- 1
Full-text search tokenizes text into words, stems them, and indexes for fast lookup. MySQL: FULLTEXT index + MATCH() AGAINST(). PostgreSQL: tsvector + tsquery + GIN index. SQLite: FTS5 virtual table. Features: word matching, phrase matching, ranking (relevance score), stemming, stop words, synonyms.
- 2 Formateur SQL →
Formater full-text search queries. MySQL: SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE). PostgreSQL: SELECT * FROM articles WHERE to_tsvector(body) @@ to_tsquery('database'). Format makes the search expression clear.
- 3 Testeur de regex →
For simple text search without full-text indexes, use LIKE or regex. LIKE '%word%' is slow (no index) but works everywhere. Regex is more powerful but also slow. For production search, use full-text indexes or a dedicated search engine (Elasticsearch, Meilisearch).
Questions fréquentes
- Full-text search vs LIKE?
- LIKE '%word%' scans every row (no index) — slow on large tables. Full-text search uses an inverted index — fast, supports ranking, stemming, and phrase matching. Use LIKE for simple substring matching on small tables; use full-text search for searching text content on large tables.
- Should I use SQL full-text or Elasticsearch?
- For simple search within one database, SQL full-text is sufficient and simpler. For complex search (faceted, fuzzy, multi-entity, relevance tuning), use Elasticsearch or Meilisearch. The trade-off: SQL is simpler but limited; search engines are powerful but add infrastructure complexity.