Tuesday, 2:47 PM. Sarah hits Enter on the user search box, typing “john” to find a customer account. The page shows a spinner. Thirty seconds pass. Then forty. At 45 seconds, the request times out. She tries again - same result. The support team Slack explodes with similar complaints.

What’s happening? A simple LIKE '%john%' query against the users table. In development with 1,000 user records, this query returns in 50 milliseconds. In production with 50 million records, it’s scanning every single row, character by character, looking for the substring “john” anywhere in the name field. It’s like reading through 50 million books page by page to find every mention of a word, instead of using an index.

MySQL is doing exactly what it was asked to do - but at scale, this becomes a table-locking, connection-pool-exhausting nightmare. While the query runs, it holds locks that block other operations. The application server spawns more connections to handle timeouts, quickly exhausting the connection pool. Within minutes, the entire application appears down.

This is the full-text search problem. Relational databases excel at structured queries with indexes, but they weren’t built for the fuzzy, substring-heavy queries that modern search requires.

Why This Happens

The root cause lies in a fundamental mismatch between what search engines need and what relational databases provide. When you run LIKE '%query%', MySQL has no choice but to examine every row because the wildcard at the beginning prevents it from using any index.

Here’s the failure chain:

User types "john" 
  -> SELECT * FROM users WHERE name LIKE '%john%'
    -> Full table scan of 50M rows (no index possible)
      -> 45-second query execution
        -> Table locks during scan
          -> Connection pool exhaustion
            -> Application appears down

The non-obvious insight most engineers miss: adding a database index won’t fix this. A B-tree index on the name column helps with queries like name LIKE 'john%' (prefix matching) but is useless for LIKE '%john%' (substring matching). The database literally cannot avoid reading every row.

The Naive Solution (and where it breaks)

Most engineers reach for one of two approaches when this happens: optimize the SQL query or add more database resources. Both miss the fundamental problem.

The “optimize SQL” path leads to tricks like:

• Adding LIMIT clauses (helps response time but breaks search completeness)
• Creating multiple columns with different search variations (explosive data duplication)
• Using FULLTEXT indexes with MATCH() AGAINST() (better, but still locks tables and doesn’t handle typos)