MySQL is a common choice for product backends because it is predictable, well understood, and easy to operate compared with many distributed databases. It works especially well when the workload is read-heavy, relational, and fits behind a single write primary.

The parts that matter in design discussions are the parts that break under load: InnoDB locking, primary key layout, replication lag, failover behavior, and query plans that change as data grows.

This chapter focuses on InnoDB internals, indexing, replication, failover, and the point where sharding becomes unavoidable.

The diagram below traces the full path a request takes from client applications through the proxy layer, into MySQL's execution pipeline, and out to the storage engine and its replicas.

Client applications (Web Server 1..N) don't connect to MySQL directly. They go through ProxySQL, which manages connection pooling, routing, and load balancing. This prevents "connection storms" and gives you a single place to enforce policies like read/write splitting and query rules.

From ProxySQL, writes are routed to the MySQL Primary, while reads are routed to read replicas (Replica 1/2) to scale read throughput.

Inside the primary, each query flows through MySQL's execution pipeline:

For transactional workloads, the storage engine is typically InnoDB, which handles the core durability and consistency mechanics. The Buffer Pool is the in-memory cache for table and index pages, turning random disk I/O into memory hits when it stays healthy.

The Redo Log records physical changes so MySQL can recover quickly after a crash. The primary can acknowledge commits once the redo is durable (depending on settings), even if data pages haven't been flushed yet. The Binary Log records logical replication events used by replicas and point-in-time recovery.

For replication and read scaling, the primary ships changes to replicas using the binary log (binlog) stream. This is separate from InnoDB's redo log, which is mainly for crash recovery on a single server. Replicas apply binlog events to stay in sync, enabling you to offload read traffic while keeping the primary as the source of truth for writes.

Choose MySQL when you need a reliable relational store with mature operations and straightforward read scaling. Be more cautious when the core problem is write distribution, advanced analytical SQL, or flexible document-style querying.

1.1 Choose MySQL When You Have

Read-heavy web applications

The classic MySQL use case. A typical web application reads data far more than it writes. User profiles are written once and read thousands of times. Product catalogs change rarely but are browsed constantly.

MySQL's replication model fits this pattern well: keep writes on one primary and add replicas to absorb read traffic. Replica scaling is useful, but not perfectly linear because of lag, cache behavior, and proxy/coordinator overhead.

Need for operational simplicity

MySQL has been running in production for decades. Backup, monitoring, migration, and incident-response patterns are mature. That operational familiarity is often more valuable than a longer feature list.

Structured data with relationships

Users have orders. Orders have items. Items have products. When your data naturally forms relationships, relational databases like MySQL let you query across those relationships efficiently.

The alternative, denormalizing everything for a document store, creates consistency headaches.

ACID transaction requirements

InnoDB provides full ACID compliance. A transfer from one account to another either completes fully or rolls back entirely. An order either reserves inventory and creates the order record, or neither happens.

These guarantees are built into the storage engine, not bolted on.

Existing ecosystem investment

Your ORM supports MySQL. Your monitoring dashboards understand MySQL metrics. Your team has MySQL expertise.

Switching databases has hidden costs beyond code changes: training, tooling, and operational knowledge. Sometimes the best database is the one your team knows.

1.2 When MySQL Is Not the Right Fit

MySQL is a strong default for many web applications, but it is not the best fit for every scaling or query problem.

Advanced SQL features

If your queries rely heavily on CTEs, window functions, or complex JSONB operations, PostgreSQL provides a more capable query engine.

MySQL's window function support arrived late (8.0) and remains less comprehensive. Its JSON support, while functional, lacks PostgreSQL's JSONB indexing capabilities.

Extreme write throughput

Classic MySQL replication can lag when replicas cannot apply binlog events as fast as the source produces them. Modern MySQL supports parallel replication, but apply throughput and lag are still important considerations.

For workloads with millions of writes per second, databases designed for write-heavy patterns like Cassandra or ScyllaDB handle the load more naturally.

Native horizontal scaling

MySQL scales vertically well and handles read scaling through replicas, but write scaling requires sharding.

Unlike databases that handle sharding internally (CockroachDB, TiDB, Cassandra), MySQL sharding requires external coordination through tools like Vitess or application-level logic.

Complex data types

PostgreSQL's support for arrays, ranges, and custom types is more mature. If your domain naturally includes these types (scheduling with ranges, tagging with arrays), PostgreSQL provides better primitives.

Sophisticated geospatial queries

MySQL has spatial extensions, but PostGIS with PostgreSQL offers a more complete geospatial toolkit. For applications where location queries are central rather than incidental, PostGIS is the stronger choice.

1.3 Common Interview Systems Using MySQL

One of MySQL's distinctive architectural choices is its pluggable storage engine layer. The SQL parser, optimizer, and connection handling are separate from the component that stores and retrieves data. This means different tables can use different storage engines, each with different characteristics.

This flexibility mostly matters for MySQL history and the occasional legacy system. For modern transactional applications, use InnoDB because it provides transactions, row-level locking, MVCC, foreign keys, and crash recovery.

2.1 InnoDB (Default, Recommended)

InnoDB became the default storage engine in MySQL 5.5 (2010), reflecting its maturity and the industry's need for transactional guarantees. Today, there is rarely a reason to choose anything else.

Key features

2.2 MyISAM (Legacy)

MyISAM was the default before MySQL 5.5. Still used for specific cases.

Key features

2.3 When to Use Each

Modern recommendation: Use InnoDB for everything. MyISAM's advantages have been eliminated in recent MySQL versions.

InnoDB internals explain the practical behavior people notice under load: why some queries are fast, why some writes block, why primary key choice matters, and why crash recovery can be quick even when dirty pages were still in memory.

3.1 Buffer Pool

The buffer pool is InnoDB's most important component for performance. It is an in-memory cache that holds data pages, index pages, and other metadata. When MySQL reads a row, it first checks the buffer pool. If the page is there (a "hit"), no disk I/O is needed.

If not (a "miss"), it must read from disk, which is orders of magnitude slower.

Besides data and index pages, the buffer pool also caches undo log pages (the old row versions MVCC relies on) and maintains an adaptive hash index.

The adaptive hash index is a hash table InnoDB builds automatically over frequently accessed index pages, letting hot lookups skip the B+Tree traversal and jump straight to the page. It is managed entirely by InnoDB and needs no configuration.

What matters for system design: the buffer pool should hold the hot working set. If the working set does not fit, MySQL falls back to disk much more often and read latency rises.

3.2 Redo Log (Write-Ahead Log)

How can MySQL acknowledge a commit immediately while data pages are still only in memory?

The answer is the redo log, also known as the write-ahead log (WAL). Instead of writing changed pages to their final locations on disk (which requires random I/O), MySQL writes a compact description of the change to the redo log (sequential I/O).

Redo logs work because sequential writes to the redo log are fast, the random writes to data files can happen later, and on crash MySQL can replay the redo log to recover committed transactions.

The design trade-off is durability versus throughput: flushing redo on every commit gives the strongest durability, while relaxing flush behavior improves throughput but risks losing recent commits during an OS or power failure.

The redo log assumes each data page on disk is either fully old or fully new. But a 16 KB InnoDB page can be torn during a crash if the OS writes only part of it.

InnoDB guards against this with the doublewrite buffer: before flushing a page to its final location, it first writes the page to a contiguous scratch area on disk.

If a crash leaves a page half-written, recovery restores the intact copy from the doublewrite buffer, then replays redo on top. This is why redo recovery can trust the pages it reads.

3.3 Undo Log and MVCC

A common database problem: if one transaction is reading a row while another is modifying it, what should the reader see? Locking the row until the writer commits would work but kills concurrency. Letting the reader see uncommitted changes creates inconsistent reads.

InnoDB solves this with MVCC (Multi-Version Concurrency Control). When a transaction modifies a row, InnoDB keeps the old version in the undo log. Readers see the version that was committed when their transaction started, regardless of concurrent modifications. Writers see their own uncommitted changes. Neither blocks the other.

How MVCC works:

Readers do not block writers and writers do not block readers, so each transaction sees a consistent snapshot. Old versions stay in the undo log until they are no longer needed.

3.4 Clustered Index

One of InnoDB's most important architectural decisions is the clustered index: the table data itself is stored as a B+tree, ordered by primary key. This is not just an index pointing to data stored elsewhere. The leaf nodes of the primary key index contain the actual row data.

Implications

Primary key best practices:

The difference between a query taking 50 milliseconds and 5 seconds often comes down to indexing. A missing index forces MySQL to scan every row in a table. The right index lets MySQL jump directly to the relevant rows.

But indexes are not free. Each index slows down writes because MySQL must update both the table and every affected index. Each index consumes storage. The skill lies in creating indexes that support your query patterns without creating unnecessary overhead.

4.1 B+Tree Index Structure

MySQL uses B+Tree indexes for all standard indexes, both primary and secondary. Understanding this structure explains why some queries use indexes and others do not.

Key properties

4.2 Primary vs Secondary Indexes

Because InnoDB stores table data in the clustered primary key index, secondary indexes work differently than you might expect. A secondary index does not contain a pointer to the row's physical location. Instead, it contains the primary key value.

Looking up a row through a secondary index requires two steps: find the primary key in the secondary index, then find the row in the primary index.

This two-step lookup explains why covering indexes (discussed below) are valuable: they eliminate the second step by including all needed columns in the secondary index itself.

4.3 Composite Indexes

When queries filter on multiple columns, a composite index on those columns can be far more effective than separate indexes. But column order matters critically.

Think of a composite index like a phone book sorted by last name, then first name. You can find all Smiths. You can find all Smiths named John. But you cannot efficiently find all Johns regardless of last name, because the data is not organized that way.

Leftmost prefix rule: An index on (A, B, C) can be used for queries on A, on A and B, or on A, B, and C. It cannot be used for queries filtering only on B or C.

4.4 Covering Indexes

Include all columns needed by query to avoid table lookup.

EXPLAIN shows "Using index" for covered queries:

4.5 Index Hints and FORCE INDEX

When MySQL chooses wrong index, you can hint:

Use sparingly

Hints are an operational lever, not a design decision, so they rarely belong in an interview answer. A hint usually papers over a real cause: stale statistics (fix with ANALYZE TABLE) or a schema that does not match the query. Reach for the cause first.

4.6 Full-Text Indexes

MySQL can index and search text within columns directly. This is worth knowing exists, but in a design discussion it is mostly a fallback for light search needs.

Once full-text search becomes a core feature (relevance ranking, typo tolerance, faceting), the common answer is to move that workload to a dedicated search engine like Elasticsearch or OpenSearch rather than push MySQL's full-text indexes harder.

4.7 Index Selection Guidelines

Choosing the right index approach comes down to understanding your query patterns:

4.8 Indexing Anti-Patterns

Concurrency is where database systems get hard. As soon as multiple transactions touch the same rows at the same time, you risk lost updates, inconsistent reads, or overselling inventory. Locks prevent these conflicts, but locking too aggressively can destroy throughput and increase tail latency.

InnoDB sits in the middle: it offers strong correctness guarantees while still enabling high concurrency through MVCC + fine-grained locks. Understanding how InnoDB locks work is essential if you want to design systems that behave correctly under real-world contention.

5.1 Isolation Levels

Isolation levels define what one transaction is allowed to observe about another transaction's work. Stronger isolation reduces anomalies but can increase locking and reduce concurrency.

Common anomalies

5.2 InnoDB Locking Types

InnoDB doesn't only lock "rows." It can also lock gaps between rows to prevent conflicting inserts.

Record Lock: Locks a single index record.

Gap Lock: Locks the gap between index records. Prevents inserts in the range.

Next-Key Lock: A next-key lock combines both behaviors: it locks the matching records and the gaps around them. This is one of the mechanisms InnoDB uses to reduce phantom-like anomalies in REPEATABLE READ.

5.3 Locking Reads

MySQL gives you explicit control over whether reads should acquire locks.

These are foundational tools for building correct systems: inventory deduction, job processing, seat booking, wallet transfers, and more.

5.4 Deadlock Handling

A deadlock happens when two transactions each hold locks the other needs, so neither can progress.

InnoDB handles deadlocks well. It detects them automatically, chooses a "victim" transaction to roll back, and returns an error like ERROR 1213: Deadlock found when trying to get lock.

To inspect deadlocks:

How to reduce deadlocks in practice

5.5 Optimistic vs Pessimistic Locking

There are two broad approaches to handling write conflicts.

Pessimistic Locking: Lock the row first, then update it.

Optimistic Locking: Allow concurrent reads, then detect conflicts at update time using a version column.

A good rule of thumb: if you expect many users to compete for the same rows at the same time, lock early (pessimistic). If conflicts are rare, avoid locks and handle the occasional retry (optimistic).

A single MySQL server is both a performance bottleneck and a single point of failure. Replication addresses both: replicas can serve read queries, distributing the read load, and if the primary fails, a replica can be promoted to take over.

MySQL replication is mature, widely operated, and well supported by tooling. The important design choice is which replication mode matches the durability and latency requirements.

6.1 Replication Basics

MySQL replication works by recording changes on the primary server to a binary log, then replaying those changes on replica servers. The design model is simple: writes go to one primary, replicas consume the change stream, and read traffic can be routed to replicas when stale reads are acceptable.

Replication process

The flow looks like this:

6.2 Replication Choices to Explain

The choices worth explaining are:

Most designs use one primary with multiple read replicas. Chain, multi-source, and active-active topologies are specialized; bring them up only when the requirements need them.

6.3 Replication Lag

Replication is rarely truly "instant." Lag happens for predictable reasons: large transactions with long apply times, sustained write load that replicas can't keep up with, slower disks or CPU on replicas, and network latency or throttling.

Common strategies to deal with lag:

6.4 Failover

Replication scales reads, but its other job is availability. When the primary dies, some replica has to take over writes. How that promotion happens, and how much data is lost in the process, is one of the most common follow-up questions in an interview, so it is worth being precise about the steps.

A failover, whether triggered manually or automatically, goes through the same phases:

GTIDs make promotion safe

The hard part of step 2 is knowing exactly how far each replica got. Legacy replication tracked position as a binlog file name plus an offset, which differs on every server and makes re-pointing replicas error-prone. Global Transaction Identifiers (GTIDs) give every committed transaction a cluster-wide unique ID.

Any replica can be told to follow the new primary "from wherever I left off," and it figures out which transactions it still needs. This is what makes automated failover reliable, so prefer GTID-based replication for any setup that needs failover.

Data loss depends on the replication mode

How much data the failover loses ties directly back to the replication choice from 6.2:

Avoiding split-brain

The dangerous case is when the old primary is not actually dead, just unreachable, and a replica gets promoted alongside it. Two nodes then accept writes and the data diverges. This is split-brain.

Production setups prevent it with fencing: before promoting, the orchestration layer makes sure the old primary can no longer accept writes, for example by having it step down on losing quorum, or by cutting it off at the proxy. A correct answer mentions that detection alone is not enough; the old primary must be reliably demoted.

Who orchestrates it

In practice the detect-choose-promote-redirect loop is run by tooling rather than a human: Orchestrator, MySQL's own InnoDB Cluster (Group Replication plus MySQL Router), or a managed service like Amazon RDS / Aurora that handles promotion behind a stable endpoint.

The interview-relevant point is that failover is an orchestrated process with a data-loss profile you choose in advance, not a single switch you flip.

Vertical scaling eventually hits a wall. You can only add so much CPU, RAM, and I/O to a single machine before the gains flatten out or the price becomes unreasonable. Replication helps you scale reads, but it does almost nothing for writes, because every write still funnels through the primary.

When a single primary becomes your bottleneck, sharding is the next step.

Sharding splits data across multiple database instances (shards). Each shard owns a subset of the data and handles a subset of the traffic. This distributes storage and write throughput, but it introduces operational and application complexity that you should not accept lightly.

7.1 Why Shard?

The contrast between a single overloaded primary and a sharded setup makes the motivation concrete. A 1 TB database handling 10K QPS becomes four 250 GB shards, each serving a fraction of the write traffic.

Sharding is typically justified when at least one of these becomes true:

7.2 Sharding Approaches

Range-Based Sharding

Split by contiguous key ranges

Hash-Based Sharding

Choose shard by hashing the key

Directory-Based Sharding

Use a lookup service/table to map keys to shards

7.3 Choosing a Shard Key

The shard key determines almost everything: distribution, routing, and query shape. A bad shard key can make sharding worse than a single database.

Good shard key properties

Common shard keys:

A practical heuristic: shard by the key that your application most naturally routes by ("who owns this data?").

7.4 Cross-Shard Operations

Sharding is easy when queries are "single-key, single-shard." It gets expensive when queries need data from many shards.

Strategies

7.5 Vitess for MySQL Sharding

Vitess is a popular open-source system for scaling MySQL horizontally. It sits between the application and MySQL and provides a sharding-aware database layer.

Vitess provides

It's widely used in large-scale production environments (notably built at YouTube and adopted by many others) because it reduces how much sharding logic leaks into application code.

7.6 When NOT to Shard

Sharding is powerful, but it's a "you own the complexity forever" decision. Exhaust simpler levers first:

Shard only when the single-primary architecture is the hard blocker and you're confident the access patterns justify the added complexity.

When a system slows down, it's rarely "the database" in the abstract. It's usually a small number of expensive queries doing disproportionate damage: scanning too many rows, missing the right index, sorting huge result sets, or triggering a bad join plan. One poorly indexed query can spike CPU, saturate I/O, and cascade into timeouts across the entire application.

8.1 EXPLAIN and EXPLAIN ANALYZE

You can't optimize what you don't understand. Start by inspecting how MySQL plans to execute your query.

Key EXPLAIN columns

Type values (best to worst)

As a first pass: avoid ALL on large tables, and be wary when rows is huge.

8.2 Common Query Optimizations

Use covering indexes

If a query can be answered entirely from an index (the covering indexes from 4.4), MySQL skips the table lookup. Remember that InnoDB already appends the primary key to every secondary index, so an index on (user_id, status) covers SELECT user_id, status, id ... WHERE user_id = 123 without listing id explicitly. A good signal in EXPLAIN is Extra: Using index.

Avoid SELECT *

SELECT * increases I/O and prevents some optimizations, especially when rows are wide.

Make ORDER BY + LIMIT index-friendly

If MySQL can walk an index in the needed order, it can stop early instead of sorting a large dataset.

Batch operations

Reduce round trips and transaction overhead.

8.3 Query Patterns to Avoid

Beyond the indexing anti-patterns from 4.8 (functions on indexed columns and leading wildcards), two more patterns quietly defeat indexes:

The implicit-conversion trap is the more reliable one: comparing a VARCHAR column to a number forces MySQL to convert the column value per row, which prevents index use.

The OR rewrite is situational. MySQL's index merge optimization can often use two separate indexes for an OR directly, so check EXPLAIN first; reach for the UNION form only when index merge is not kicking in.

8.4 Pagination Optimization

Offset pagination looks simple, but it gets slower as offsets grow because MySQL still has to walk past skipped rows.

For stable ordering with ties (common in feeds), use a composite cursor:

8.5 Optimization Checklist

Optimization works best as a repeatable loop rather than a grab-bag of server commands:

The important signal is that you can connect a slow user-facing path to the query plan and choose a focused fix.

Use this comparison to keep the database choice grounded in requirements:

Use MySQL for read-heavy relational applications where operational maturity and predictable behavior matter more than advanced SQL features or native distributed writes.

Anchor the answer on InnoDB, indexes, replication, and the scaling boundary. Explain why a single write primary is acceptable, how replicas handle reads, how failover works, and when sharding or a distributed alternative becomes necessary.