MySQL advice online is full of half-truths: tips that sound right, used to be true, or only apply in very specific setups. This guide breaks down common MySQL myths vs reality, with a focus on what actually changes outcomes in production.
Keep it simple: verify with your workload, measure, and prefer boring reliability over clever tricks.
Myth: “Adding indexes always makes queries faster”
Reality: Indexes speed up reads when they match your query patterns, but they also add write cost and can be ignored by the optimizer.
Indexes help when they reduce scanned rows and avoid expensive sorts. They hurt when you add “just in case” indexes that aren’t used, or when updates/inserts become slower because MySQL must maintain extra index trees.
- Check actual usage: use EXPLAIN and verify the chosen index (not just that an index exists).
- Prefer composite indexes that match common WHERE + ORDER BY patterns over many single-column indexes.
- Watch write-heavy tables: each extra index is extra work on INSERT/UPDATE/DELETE.
- Remove dead weight: periodically review unused/duplicate indexes (especially after feature changes).
A good index is a tool for a specific query shape, not a badge of “optimized.”
Myth: “SELECT * is fine if the table isn’t huge”
Reality: SELECT * often turns into silent over-fetching, wider temporary tables, and more I/O than you expect—especially once JSON, TEXT, or extra columns accumulate over time.
It also makes it easier for application changes to accidentally increase payload size or change behavior when new columns are added.
- Pick only needed columns to reduce network and buffer pressure.
- Be careful with wide rows: large variable-length columns can force extra reads.
- It’s not just size: fewer columns can allow covering indexes for some queries.
“It’s fine today” becomes “why is this endpoint slow?” six months later.
Myth: “InnoDB is always slower than MyISAM”
Reality: InnoDB is the default for a reason: transactions, crash recovery, and row-level locking usually win in real systems. MyISAM can look fast in narrow benchmark cases, but it trades away reliability and concurrency.
If you need safe writes, consistent reads, and the ability to recover cleanly after failure, InnoDB is typically the practical choice on the web.
- InnoDB: ACID transactions, row locks, foreign keys, better crash recovery.
- MyISAM: table locks, no transactions, fragile after crashes (repair scenarios).
Speed without safety is often just deferred downtime.
Myth: “A daily dump is a solid backup strategy”
Reality: A single daily logical dump can be better than nothing, but it’s not the same as a recovery plan. Real backups are about restore time, point-in-time recovery, and proving you can restore.
- Test restores regularly: an untested backup is a hope, not a strategy.
- Know your RPO/RTO: how much data can you lose (RPO), how fast must you restore (RTO).
- Consider binary logs: binlog + a base backup enables point-in-time recovery.
- Store off-host: keep copies outside the database server (and ideally in another zone/region).
Backups are only “good” if you can restore the right data in time.
Myth: “If it’s slow, just increase RAM and cache”
Reality: More memory can help, but it won’t fix bad query shapes, missing indexes, or lock contention. Many slowdowns are caused by reading too many rows, sorting large result sets, or waiting on writes.
A practical triage order is: (1) find the query, (2) confirm plan + rows examined, (3) fix the access pattern, then (4) tune memory for stability.
- Look for ‘rows examined’ being wildly higher than returned rows.
- Check locks: slow can mean waiting, not computing.
- Spot temp tables/filesort: often a sign the index doesn’t match filtering and ordering.
- Then tune: buffer pool sizing, connection limits, and per-session memory.
Hardware is great at masking problems—until traffic grows again.
Myth: “Transactions guarantee consistency automatically”
Reality: Transactions help, but you still need to choose the right isolation level, handle deadlocks, and be careful with multi-step application logic.
- Deadlocks are normal: design your app to retry safe transactions.
- Isolation levels matter: “repeatable read” vs “read committed” changes what anomalies you can see.
- Keep transactions short: long transactions hold locks and increase contention.
- Be explicit about ordering: consistent update order reduces deadlock risk.
A transaction is not a magic shield; it’s a tool with rules.
Takeaway: a quick “reality-first” checklist
- Measure before changing: EXPLAIN + slow query logs beat guessing.
- Index for real queries: fewer, better indexes usually win.
- Prefer reliability defaults: InnoDB, tested restores, sane isolation choices.
- Fix query shape first: then tune memory and capacity.
If you treat MySQL as a system (queries + schema + operations), most “mysteries” turn into predictable tradeoffs.