When should you prefer REGEXP over full-text search or LIKE, and when should you avoid it?

You need simple wildcard matching: prefix, suffix, substring search.

Performance matters: LIKE with indexed prefixes (column LIKE 'text%') can use BTREE indexes.

The search is predictable: no need for complex logic.

You need fast search on large text columns: FULLTEXT indexes are optimized for big datasets.

Search is word-based: relevance ranking, natural language queries, boolean mode queries.

You search millions of rows: full-text avoids full table scans.

Performance is critical: REGEXP forces full table scans and is slow on large datasets.

You can use full-text indexing: for long articles, blog posts, comments, logs.

The search can be rewritten using LIKE: to leverage indexing.

Patterns are too complex: heavy backtracking can degrade query speed drastically.

The operation is frequent: repeated REGEXP filtering on large tables becomes expensive.