LIKE %abc% and Why Your Query Got 200 Times Slower

A percent sign in the wrong place turns a 0.02-second query into a 4-second one. And nothing on screen tells you.

The problem

Find every employee whose name starts with “Ng”. Two wildcards do all the work:

• % — any number of characters, including none
• _ — exactly one character

SELECT * FROM employees WHERE name LIKE 'Ng%';

Common mistakes

1. A leading %

WHERE name LIKE '%an%'

An index is sorted alphabetically, so it can only jump to a prefix. Starting with % forces a row-by-row scan. On five million rows that is the difference between 0.02 seconds and 4 seconds.

If you genuinely need mid-string search, use full-text search rather than bending LIKE to do it.

2. Assuming LIKE is case sensitive

It depends on the engine. MySQL is case-insensitive by default; PostgreSQL is case-sensitive. To be certain:

WHERE LOWER(name) LIKE 'ng%'     -- but this loses the index
WHERE name ILIKE 'ng%'           -- PostgreSQL, index-friendly with the right index

3. Forgetting to escape

Searching for a literal %:

WHERE promo_code LIKE '%\%%' ESCAPE '\'

4. Using LIKE for exact matching

WHERE code = 'A001'         -- fast
WHERE code LIKE 'A001'      -- slower, buys you nothing

When you need more: regular expressions

-- Names starting with a vowel (PostgreSQL)
WHERE name ~* '^[aeiou]'

-- MySQL
WHERE name REGEXP '^[aeiou]'

This is exactly the shape of HackerRank’s Weather Observation Station 6 through 12.

Practice

• HackerRank — Weather Observation Station 6, 7, 8, 9, 10, 11, 12
• LeetCode 1667 — Fix Names in a Table
• LeetCode 1517 — Find Users With Valid E-Mails