150 columns, no joins, no foreign keys to chase - every developer's secret dream. Until an UPDATE to last_login_at rewrites 6KB of row on every sign-in and the buffer pool holds four customers per page.
A query runs in 0.4 ms for a year as an index-only scan with heap fetches at zero. A feature request adds one column to the SELECT list. The next day the same query takes 1243 ms. Nothing else changed. The index is the same, the filter is the same, the data is the same. The select list just stopped being covered.
tmp_orders is the main orders table. old_price holds the current price. flag1 means something nobody remembers. Every mature schema drifts this way: names that stopped describing their data, conventions from three different eras, tables whose temporary prefix is locked in permanently. The fix isn't renaming; it's making the drift legible to the next reader.
WHERE YEAR(created_at) = 2025 scans every row in the table. WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' does an index range scan. Both return identical rows; one is orders of magnitude faster. The difference is a single function call that the query planner can't see past.
An AI assistant generates a join on u.id = a.id, the query runs clean, returns zero rows. The assistant reports 'no data.' The real answer: users.id is BIGINT, actions.id is a UUID, and MySQL silently coerced the mismatch into an empty result. The footgun is the column name, not the type behind it.
resource_id BIGINT, resource_type VARCHAR(50), no REFERENCES clause, because the ID can point to orders, invoices, tickets, or anything else, depending on what the sibling column says today. ORMs make this a one-liner. The database can't enforce any of it: no FK, no cascade, no planner metadata, no schema-level description of what the column actually references.
Year five of a Rails project: four migration directories, a User class with thirty custom methods overriding the ORM defaults, a quarterly meeting about whether to upgrade Rails 4 to 7 or migrate off entirely. The schema is fine. The thing built around it isn't.
A schema with three ways to spell `created_at` (`createdAt`, `created_date`, `date_created`), four PK strategies (BIGINT here, UUID there, two flavors of composite key in the analytics tables), and a deleted_at column on 80% of tables: the 20% that don't have it are the ones whose queries silently return soft-deleted rows. Nobody broke a rule. There was no rule to break.
A counter-cache trigger fires on every comment insert and serializes every concurrent write on the parent post's row lock. A CHECK constraint and an application validator drift apart over five years until a migration tightening the constraint fails on 4,000 legacy rows. Where each rule belongs comes down to scope, cadence, cost, and how many things write to the schema.
An on-call engineer reads `LATEST DETECTED DEADLOCK` and sees `lock_mode S locks rec but not gap` on the unique index. That's a duplicate-key conflict on insert, not the lock-ordering bug everyone assumed. Tuning the retry limit would have hidden it for another quarter. Reading the log first is what separates fixes from cosmetics.