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.
LIKE filters strings against a pattern. Two wildcards: % matches zero or more characters, _ matches exactly one character. Everything else in the pattern is a literal match.
Common patterns
-- Names starting with 'Al'
SELECT * FROM users WHERE last_name LIKE 'Al%';
-- Names ending with 'son'
SELECT * FROM users WHERE last_name LIKE '%son';
-- Exactly 5-character codes
SELECT * FROM products WHERE sku LIKE '_____';
-- 'an' anywhere in the name
SELECT * FROM users WHERE last_name LIKE '%an%';Case sensitivity
LIKE is case-insensitive in MySQL and SQL Server by default, but case-sensitive in PostgreSQL (use ILIKE for case-insensitive matching in Postgres).
The index problem
-- GOOD: leading literal lets the optimizer use an index on last_name
WHERE last_name LIKE 'Smith%'
-- BAD: leading % prevents index seek — full scan on every row
WHERE last_name LIKE '%Smith%'A B-tree index is sorted like a dictionary. To use it, the database must know the starting characters. A leading % hides those characters, so the engine reads every row.
For substring search at scale, the right tool is a full-text index (FULLTEXT in MySQL, GIN with pg_trgm in PostgreSQL), not LIKE '%term%'.
Try the patterns yourself
Run the query and experiment with different patterns. Notice how 'Al%' hits the index (conceptually) while '%son' would not.
Escaping wildcards
To search for a literal % or _ character, escape it:
-- Find discount codes that literally contain '%'
WHERE code LIKE '50\%' ESCAPE '\';