An app almost never gets slow all at once. It gets slow one unnoticed query at a time, until a table crosses some row count and everything that used to feel instant starts taking a full second. These are the seven patterns I run into most often when I get called in to fix a "why is this suddenly so slow" problem — and they're almost always already in the codebase from day one, just invisible at low data volume.

1. SELECT * When You Only Need Three Columns

SELECT * pulls every column across the network and through the ORM's hydration layer, even the ones you never touch — including large text or blob columns sitting in that same row. Selecting only the columns you actually use shrinks the result set, reduces memory pressure, and often lets MySQL satisfy the query straight from an index instead of touching the full row at all.

2. N+1 Queries From ORM Laziness

This is the single most common one I find. You loop over 50 orders and, for each one, the ORM lazily fires a separate query to fetch its customer. That's 51 round trips to the database for something that should have been 2. Eager-loading the relationship up front turns an O(n) query pattern into a constant number of queries, and it's usually a one-line fix once you know to look for it.

If a page's load time scales with the number of rows on it rather than staying flat, N+1 queries are the first thing I check. It's almost always the answer.

3. Missing Indexes on Columns You Filter or Join On

Without an index, MySQL has to scan every row in the table to find matches — fine at 500 rows, brutal at 500,000. Any column that regularly shows up in a WHERE, JOIN, or ORDER BY clause is a candidate for an index. The cost is a small amount of extra disk space and slightly slower writes; the benefit is queries that stay fast as the table grows instead of degrading linearly with row count.

4. LIKE '%term%' Instead of Full-Text Search

A leading wildcard in a LIKE clause can't use a standard index at all, because MySQL has no way to know where in the string the match might start. For real search functionality — searching inside descriptions, names, or comments — a full-text index (or a dedicated search service for larger datasets) will outperform a wildcard LIKE by an order of magnitude once the table has any real size to it.

5. Paginating Large Tables With OFFSET

LIMIT 20 OFFSET 100000 forces MySQL to walk through and discard 100,000 rows before it can return the 20 you actually want — and that cost grows with every page the user clicks. Keyset pagination (filtering on "give me the next 20 rows after this ID" instead of "skip N rows") keeps page load times flat regardless of how deep into the dataset you are.

Why This One Gets Missed

  • It performs fine in development and QA, where tables are small
  • It only becomes visibly slow once a table has tens of thousands of rows
  • By the time it's noticed, it's usually in production and under load

6. Storing JSON Blobs You Then Query Inside Of

MySQL's JSON column type is genuinely useful for data you store and retrieve whole. It becomes a performance problem when you start regularly filtering or sorting on a specific key buried inside that JSON — those queries can't use a normal index and force MySQL to parse the JSON on every row. If you're querying a field often enough to care about its speed, it usually belongs in its own indexed column.

7. No Query Plan Review Before Shipping

Running EXPLAIN on a query before it ships takes a few seconds and tells you plainly whether MySQL is using an index or scanning the full table. I run it on any query that touches a table I expect to grow, and it's caught more slow-query incidents for me before launch than any amount of guessing about "what should be fast."

Takeaway

None of these are exotic — they're all things that look completely fine in a small development database and only reveal themselves once real data volume shows up. Reviewing for them before that happens is far cheaper than firefighting a production slowdown after the fact.