REGEXP is powerful for pattern-based searching, but it is slower and more CPU-intensive than LIKE or full-text search. Choosing the right tool depends on your use case and performance requirements.

Use REGEXP When

1
You need complex pattern matching: such as character classes, optional parts, alternations, boundaries, digits, Unicode classes.
2
Validating structured data: emails, phone numbers, codes, IDs, SKU patterns.
3
Detecting multiple possible formats: example: dates in dd/mm/yyyy OR yyyy-mm-dd.
4
Extracting or filtering on flexible text rules: logs, user input, free-form fields.

Use LIKE When

1
You need simple wildcard matching: prefix, suffix, substring search.
2
Performance matters: LIKE with indexed prefixes (column LIKE 'text%') can use BTREE indexes.
3
The search is predictable: no need for complex logic.

Use Full-Text Search When

1
You need fast search on large text columns: FULLTEXT indexes are optimized for big datasets.
2
Search is word-based: relevance ranking, natural language queries, boolean mode queries.
3
You search millions of rows: full-text avoids full table scans.

Avoid REGEXP When

1
Performance is critical: REGEXP forces full table scans and is slow on large datasets.
2
You can use full-text indexing: for long articles, blog posts, comments, logs.
3
The search can be rewritten using LIKE: to leverage indexing.
4
Patterns are too complex: heavy backtracking can degrade query speed drastically.
5
The operation is frequent: repeated REGEXP filtering on large tables becomes expensive.

Example: When REGEXP Is Appropriate

Example: When LIKE Performs Better

Example: When Full-Text Search Is Ideal

Difficulty: 5/10

Topics: REGEXP vs LIKE, Full-text search, Performance considerations

Scenario Questions

0-2 years experience

1
Suppose you need to find all rows where a column contains a phone number pattern like (123) 456-7890. How would you write the query, and why would you choose REGEXP over LIKE?
2
If you run a REGEXP query on a table with 10,000 rows and notice it takes noticeably longer than a simple LIKE, what could be causing the slowdown?

2-5 years experience

1
Your team added a feature to search product descriptions using a user‑provided regex. After deployment, the search page becomes slow on the production dataset of millions of rows. Walk me through how you would diagnose and decide whether to keep REGEXP or switch to full‑text search.
2
During a code review you see a query that uses REGEXP to filter email addresses, but the same column is also indexed for full‑text search. Explain the trade‑offs and which approach you’d recommend.

5-8 years experience

1
Design a solution for a high‑traffic e‑commerce site that needs to support both fuzzy keyword search and complex pattern matching on product tags. How would you combine MySQL REGEXP, full‑text indexes, and possibly external search services, and where would you draw the line on using REGEXP?
2
Your database has grown to 500 M rows and you notice REGEXP queries causing CPU spikes. Propose a migration plan to replace those with a more scalable approach, considering data consistency and downtime.

8+ years experience

1
At a large SaaS company, legacy code heavily relies on REGEXP for log‑analysis queries. Management wants to reduce operational cost and improve latency. How would you evaluate the long‑term strategy—refactor to full‑text, move to a dedicated search engine, or keep REGEXP with sharding? Discuss cross‑team impact and migration risks.
2
Imagine you are defining the data‑access standards for all microservices in your organization. What guidelines would you set regarding when to use REGEXP versus LIKE versus full‑text, and how would you enforce them across teams to avoid performance pitfalls?

Follow-up Questions

❓Can you give an example of a pattern that would be inefficient with REGEXP?
❓How does MySQL's optimizer treat REGEXP compared to LIKE?
❓What indexing options exist for REGEXP, if any?