How to Use the LIKE Operator in MySQL Database

LIKE is probably the single most misused operator I encounter when reviewing other people’s SQL — not because it’s hard to write, but because it’s deceptively easy to write in a way that silently forces a full table scan on a large table. I’ve fixed more slow search features caused by careless LIKE patterns than almost any other single issue. In this guide, I’ll cover the syntax, the wildcard behavior, the internals of when LIKE can and can’t use an index, and the alternatives I reach for when LIKE isn’t the right tool.

Basic Syntax

SELECT id, name, email
FROM users
WHERE name LIKE 'John%';

This finds every name starting with “John” — John, Johnny, Johnathan, and so on. LIKE uses two wildcard characters:

• % — matches zero or more characters
• _ — matches exactly one character

The Wildcard Patterns

-- Starts with 'John'
WHERE name LIKE 'John%'

-- Ends with 'son'
WHERE name LIKE '%son'

-- Contains 'oh' anywhere
WHERE name LIKE '%oh%'

-- Exactly 4 characters, starting with 'J'
WHERE name LIKE 'J___'

-- Second character is 'a'
WHERE name LIKE '_a%'

Case Sensitivity

By default, LIKE in MySQL is case-insensitive when the column uses a _ci (case-insensitive) collation, which is the default for most text columns:

SELECT * FROM users WHERE name LIKE 'john%';
-- Matches 'John', 'JOHN', 'john', etc. under a _ci collation

If I need case-sensitive matching, I explicitly specify a _cs (case-sensitive) or binary collation:

SELECT * FROM users WHERE name LIKE 'John%' COLLATE utf8mb4_bin;

Escaping Wildcard Characters

If I actually need to search for a literal % or _ character (say, matching a discount code containing a percent sign), I escape it:

SELECT * FROM promotions WHERE code LIKE '50\%OFF' ESCAPE '\';

By default, \ is already the escape character in MySQL string literals, so often I can write:

SELECT * FROM promotions WHERE code LIKE '50\%OFF';

I always test this carefully though, because escape character behavior can vary depending on the sql_mode settings (NO_BACKSLASH_ESCAPES disables this behavior entirely).

How LIKE Interacts with Indexes — The Most Important Part

This is the section I wish every developer read before shipping a search feature.

graph TD
    A[LIKE pattern] --> B{Does pattern start with a wildcard?}
    B -->|No - e.g. 'John%'| C[Index can be used - range scan on leading characters]
    B -->|Yes - e.g. '%John' or '%John%'| D[Index cannot be used efficiently - full table/index scan]

• LIKE 'John%' (leading characters fixed, wildcard at the end) — MySQL can use a standard B-tree index here, because it can jump directly to entries starting with “John” and scan forward, the same way a phone book lets you jump to the “J” section.
• LIKE '%John' or LIKE '%John%' (wildcard at the start) — MySQL cannot use a standard B-tree index efficiently, because there’s no way to know where in the index “contains John somewhere” entries would start. This forces a full table or full index scan.

Verifying with EXPLAIN:

EXPLAIN SELECT id, name FROM users WHERE name LIKE 'John%';

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | idx_name      | idx_name | 202     | NULL | 4    | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

EXPLAIN SELECT id, name FROM users WHERE name LIKE '%John%';

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 500000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Same table, same column, same general intent — but type: ALL scanning 500,000 rows versus type: range scanning 4. That’s the difference between milliseconds and potentially seconds on a large table, purely based on where the wildcard sits in the pattern.

What I Use Instead of Leading-Wildcard LIKE for Search

When I genuinely need “contains” search (not just “starts with”), I don’t rely on LIKE '%term%' against a large table. My options, roughly in order of preference:

1. Full-Text Search (Built Into MySQL)

ALTER TABLE articles ADD FULLTEXT INDEX idx_content_fulltext (title, body);

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

Full-text indexes are purpose-built for this — they tokenize content into words and build an inverted index, so “contains this word anywhere” queries are fast, unlike leading-wildcard LIKE.

graph LR
    A[Full-text index] --> B[word: database -> doc IDs 4, 12, 87]
    A --> C[word: performance -> doc IDs 4, 55, 87]
    D[MATCH AGAINST query] --> E[Intersect/rank matching doc ID sets]

2. Dedicated Search Engines

For serious search functionality — typo tolerance, relevance ranking, faceted search — I reach for Elasticsearch, OpenSearch, or Meilisearch rather than asking MySQL to do something it wasn’t designed for.

3. Generated/Trigram Indexes (Advanced)

For specific “contains” use cases at scale where full-text search doesn’t fit (like partial matches on structured codes), some teams build trigram-based indexing strategies, though I only reach for this in fairly specialized situations.

When Plain LIKE Is Still the Right Tool

I don’t want to overstate the danger here — LIKE is completely fine and often the simplest correct choice when:

• The table is small (a few thousand rows or fewer)
• The pattern is a prefix match ('John%'), which can use an index
• It’s an infrequent admin/reporting query, not a hot user-facing path
• You’re filtering an already-narrowed result set (after a WHERE on an indexed column limits rows to a small number first)

SELECT id, name FROM users WHERE department_id = 12 AND name LIKE '%smith%';

Here, department_id = 12 (assuming it’s indexed and selective) narrows the row count first, and the LIKE '%smith%' filter only runs against that smaller set — usually acceptable in practice.

Combining LIKE with Other Conditions

SELECT id, name, email
FROM users
WHERE name LIKE 'A%'
  AND status = 'active'
ORDER BY name;

I always order conditions in my head (though MySQL’s optimizer handles the actual execution order) by which is most selective — if status = 'active' filters out 95% of rows and has an index, I make sure that index exists so the LIKE only evaluates against the remaining rows.

Security Considerations

Just like every other operator taking user input, LIKE patterns must be parameterized — and there’s a specific extra step I always take: escaping user-supplied wildcard characters so a search for a literal percent sign doesn’t behave like an unintended wildcard.

// Node.js example
function escapeLikePattern(input) {
  return input.replace(/[%_\\]/g, '\\$&');
}

const searchTerm = escapeLikePattern(userInput);
const query = 'SELECT id, name FROM products WHERE name LIKE ?';
connection.query(query, [`%${searchTerm}%`]);

Without this escaping step, a user searching for “100% cotton” would have their literal % interpreted as a wildcard, returning unexpected results — not a security hole exactly, but definitely a correctness bug I’ve had to fix in production search boxes before.

Real-World Scenario: Fixing a Slow Search Feature

A client’s product search endpoint was taking 3-4 seconds per request once their catalog crossed around 200,000 SKUs, because the search box used LIKE CONCAT('%', ?, '%') against the name column with no other filtering. We migrated the search to a MySQL FULLTEXT index for natural-language relevance matching on product names and descriptions, which brought response times down to under 100ms for typical queries. For a couple of edge cases needing true substring matching on SKU codes (not natural language), we kept a narrowly scoped LIKE query that only ran against products already filtered by category — an acceptable trade-off since that path had far fewer candidate rows to scan.

Performance Tips

1. Never start a LIKE pattern with % on a large, frequently-queried table without first narrowing the row set through an indexed filter.
2. Use FULLTEXT indexes for genuine “contains this word” search rather than leading-wildcard LIKE.
3. Prefix searches ('term%') can use a normal B-tree index — prefer this pattern whenever the UX allows it (e.g., autocomplete-style search).
4. Escape user-supplied % and _ to avoid unintended wildcard behavior.
5. Consider a dedicated search engine once your search requirements grow beyond simple pattern matching.

Troubleshooting Common Issues

Frequently Asked Questions

Is LIKE case-sensitive in MySQL? By default, no — it follows the column’s collation, which is typically case-insensitive (_ci) unless explicitly changed.

Can LIKE ever use an index with a leading wildcard? Not with a standard B-tree index. Some storage engines or specialized index types (trigram-based, for example) can help, but the built-in behavior for %term% is a full scan.

What’s the difference between LIKE and REGEXP in MySQL? LIKE supports only two simple wildcards (% and _); REGEXP supports full regular expression matching, but is generally slower and even less index-friendly than LIKE.

Should I use LIKE or FULLTEXT for a search box? For anything beyond a small table or a simple prefix match, I use FULLTEXT (or an external search engine) rather than a leading-wildcard LIKE.

Interview Questions on This Topic

1. Why can MySQL use an index for LIKE 'term%' but not for LIKE '%term%'?
2. What’s the difference between the % and _ wildcards in a LIKE pattern?
3. How does a FULLTEXT index solve the performance problem that leading-wildcard LIKE queries create?
4. Why is it important to escape user-supplied wildcard characters in a search feature?
5. When would plain LIKE still be an acceptable choice despite its indexing limitations?

Key Takeaways

• LIKE wildcards at the end of a pattern ('term%') can use a standard index; wildcards at the start ('%term%') generally cannot.
• Case sensitivity depends on the column’s collation, not LIKE itself.
• For real “contains anywhere” search at scale, use MySQL’s FULLTEXT indexing or a dedicated search engine instead of leading-wildcard LIKE.
• Always escape user-supplied wildcard characters to avoid incorrect matching behavior.
• LIKE is still perfectly fine for small tables, prefix searches, or queries already narrowed by another indexed filter.

References

• MySQL 8.0 Reference Manual — String Comparison Functions (LIKE): https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like
• MySQL 8.0 Reference Manual — Full-Text Search Functions: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
• MySQL 8.0 Reference Manual — Pattern Matching: https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html