The Query That Eats Your Database While Everyone Sleeps

Ask any engineer who's inherited a production database what scares them, and they won't say dropouts or disk failures. They'll say the query that's been running fine, until it doesn't. SQL debt is uniquely creepy because it's invisible. The app works. The dashboards are green. Meanwhile, somewhere in the dark, a query is doing a full table scan across 40 million rows every time someone loads their profile.

Here are the seven habits that build these haunted houses — and how to start cleaning them out without the whole thing collapsing.

1. The SELECT * Reflex

Why it happens: it's faster to type, tutorials use it everywhere, and 'we'll trim the columns later.' Later never comes.

How it haunts you: SELECT * drags every column across the network, including the 2KB blob column nobody's touched since the migration. It also breaks the moment someone adds a column, silently shifts your ORM mappings, and prevents covering-index optimizations entirely.

The fix: explicit column lists, always. In a big legacy codebase, grep for SELECT * and work through them a file at a time. It's tedious and it's the single highest-leverage cleanup you'll do all quarter.

2. The N+1 Query Plague

Why it happens: ORMs make it invisible. You loop over users and access user.orders, and the ORM helpfully fires one query per user. In dev, with 12 rows, nobody notices.

How it haunts you: in production, with 12,000 rows, you've turned one round trip into 12,001. Your database connection pool chokes, latency spikes, and the on-call engineer gets a page at 3 AM.

The fix: learn your ORM's eager-loading feature (include, select_related, preload — every framework has one) and learn to read its query logs. Most ORMs will happily show you every query they fire. Look once and you'll never unsee it.

3. Missing Indexes on the Columns That Matter

Why it happens: indexes feel like a DBA problem, and 'we'll add them when it gets slow.'

How it haunts you: the query that ran in 40ms on day one runs in 40 seconds once the table crosses a few million rows. Full table scans scale linearly; indexes scale logarithmically. The gap between those two curves is your incident report.

The fix: find the slow query log (every major engine has one), sort by frequency times duration, and index the columns in your WHERE and JOIN clauses. Don't index everything — each index slows writes. Index what the app actually filters on.

4. Indexes You Have But Don't Use

The inverse haunting: someone built an index on LOWER(email) in 2021, but the application queries WHERE email = ?. The index sits there, slowing every insert, helping no one. Run your engine's query analyzer (EXPLAIN ANALYZE) on your hottest queries and confirm the plan actually uses what you built. Unused indexes are pure tax.

5. The 'Temporary' Table That Became Load-Bearing

Why it happens: someone needed a staging area for a one-off migration. They named it tmp_users_backup_final. That was three years ago.

How it haunts you: it bloats backups, confuses every new engineer, and — worst case — some cron job you don't know about still reads from it. Now it's not temporary; it's undocumented infrastructure.

The fix: audit for tables matching tmp, temp, backup, old, and _final. Before dropping anything, check query logs and view definitions for references. Then drop them, and write down what you found. Future-you is a new hire with no context.

6. Business Logic Living in Queries

Why it happens: a 300-line query with three CTEs and a window function is faster to write than a proper service layer, especially under deadline.

How it haunts you: nobody can modify it safely. The one person who understands it left for a startup in Austin. Every change is archaeology.

The fix: you don't need to rewrite it all at once. Wrap the query, add tests around its outputs, then peel it apart one CTE at a time. Characterization tests turn a haunted artifact into a refactorable one.

7. No Query Budget, No Monitoring

The meta-habit that enables all the others: nobody knows how long queries take until something is already on fire. Add a slow-query alert at a reasonable threshold, track your p95 query latency like you track error rates, and make query review part of code review. Databases don't fail suddenly — they degrade politely for months while nobody watches.

Start Small, Start Tonight

You don't fix a haunted database in a sprint. Pick one anti-pattern, find its worst instance, fix that one, and write it up. Share it with the team. Databases respond to the same medicine as any legacy system: small, verified, documented changes, applied consistently. The ghosts leave one room at a time.