How does the LIKE operator work in SQL, and when is it a performance problem?
LIKE matches string patterns using % (any sequence of characters) and _ (exactly one character). A leading wildcard like '%smith' forces a full table scan because the index cannot be used from the left side; a trailing wildcard like 'smith%' can use a B-tree index prefix scan.
How to think about it
The interviewer wants two things: do you know the wildcards, and do you know the performance trap? Lead with the semantics, then pivot to the index problem — that pivot is what marks a candidate who’s run LIKE on a real table.
LIKE matches a string against a pattern. Two wildcards: % matches zero or more characters, _ matches exactly one. Everything else is a literal.
A worked example — the wildcards in action
Each pattern selects a different slice of the same six surnames:
SELECT id, last_name FROM users WHERE last_name LIKE 'Al%';| id | last_name |
|---|---|
| 1 | Alvarez |
| 5 | Al_bert |
'Al%' — starts with “Al”. A suffix match flips which rows come back, and _ pins an exact length:
SELECT id, last_name FROM users WHERE last_name LIKE '%son'; -- ends with "son"
SELECT id, last_name FROM users WHERE last_name LIKE '__ith'; -- 5 chars ending "ith"| %son → id | last_name |
|---|---|
| 2 | Anderson |
| 3 | Johnson |
| 4 | Jackson |
| __ith → id | last_name |
|---|---|
| 6 | Smith |
'%son' catches the three “-son” names; '__ith' matches only Smith — two single-character wildcards plus the literal “ith” is exactly five characters.
Escaping wildcards
To match a literal % or _, declare an escape character. Without it, 'Al_%' would treat the _ as “any one character”; escaping makes it literal:
SELECT id, last_name FROM users WHERE last_name LIKE 'Al\_%' ESCAPE '\';| id | last_name |
|---|---|
| 5 | Al_bert |
Only Al_bert (which truly contains an underscore) matches — Alvarez is excluded.
The index problem
This is the half that matters at scale. A B-tree index is sorted like a dictionary, so the engine needs the starting characters to seek into it:
WHERE last_name LIKE 'Smith%' -- GOOD: leading literal, index prefix seek
WHERE last_name LIKE '%Smith%' -- BAD: leading %, hides the start, full scanA leading % hides those first characters, so the database falls back to reading every row. LIKE is also case-insensitive by default in MySQL and SQL Server but case-sensitive in PostgreSQL (use ILIKE there).