{"title":"MySQL Deep Dive","description":"","content":"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.\n\nThe 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.\n\n\n\n\n\nThis chapter focuses on InnoDB internals, indexing, replication, failover, and the point where sharding becomes unavoidable.\n\n---\n\n# MySQL Architecture Overview\n\nThe 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.\n\n\n```mermaid\nflowchart TD\n subgraph Client[\"Client Applications\"]\n A1[Web Server 1]:::primary\n A2[Web Server 2]:::primary\n A3[Web Server N]:::primary\n end\n\n subgraph Proxy[\"Connection Management\"]\n PX[ProxySQL]:::orange\n end\n\n subgraph Primary[\"MySQL Primary\"]\n SE[SQL Parser]:::green\n OPT[Query Optimizer]:::green\n EX[Execution Engine]:::green\n INNO[InnoDB Storage Engine]:::teal\n BP[Buffer Pool]:::teal\n RL[Redo Log]:::teal\n BL[Binary Log]:::teal\n end\n\n subgraph Replicas[\"Read Replicas\"]\n R1[Replica 1]:::teal\n R2[Replica 2]:::teal\n end\n\n A1 --> PX\n A2 --> PX\n A3 --> PX\n PX -->|Writes| SE\n PX -->|Reads| R1\n PX -->|Reads| R2\n SE --> OPT --> EX\n EX --> INNO\n INNO --> BP\n INNO --> RL\n INNO --> BL\n BL -->|Replication events| R1\n BL -->|Replication events| R2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nClient 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.\n\nFrom ProxySQL, writes are routed to the **MySQL Primary**, while reads are routed to **read replicas** (Replica 1/2) to scale read throughput.\n\nInside the primary, each query flows through MySQL's execution pipeline:\n\n1. **SQL Parser** parses the SQL text, validates syntax, and builds an internal query representation.\n2. **Query Optimizer** chooses an execution plan (which index to use, join order, access paths, etc.).\n3. **Execution Engine** runs that plan and calls into the storage engine to read/write rows.\n\nFor 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.\n\nThe **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.\n\nFor 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.\n\n---\n\n# 1. When to Choose MySQL\n\nChoose 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.\n\n### 1.1 Choose MySQL When You Have\n\n#### Read-heavy web applications\n\nThe 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. \n\nMySQL'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.\n\n#### Need for operational simplicity\n\nMySQL 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.\n\n#### Structured data with relationships\n\nUsers 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. \n\nThe alternative, denormalizing everything for a document store, creates consistency headaches.\n\n#### ACID transaction requirements\n\nInnoDB 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. \n\nThese guarantees are built into the storage engine, not bolted on.\n\n#### Existing ecosystem investment\n\nYour ORM supports MySQL. Your monitoring dashboards understand MySQL metrics. Your team has MySQL expertise. \n\nSwitching databases has hidden costs beyond code changes: training, tooling, and operational knowledge. Sometimes the best database is the one your team knows.\n\n\n```mermaid\nflowchart TD\n A[Need relational database?]:::primary\n A -->|Yes| B[Read-heavy workload?]:::primary\n A -->|No| C[Consider NoSQL]:::orange\n B -->|Yes| D[Need simple scaling?]:::primary\n B -->|No| E[Consider PostgreSQL]:::orange\n D -->|Yes| F[MySQL is excellent]:::green\n D -->|No| G[MySQL with sharding]:::orange\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n### 1.2 When MySQL Is Not the Right Fit\n\nMySQL is a strong default for many web applications, but it is not the best fit for every scaling or query problem.\n\n#### Advanced SQL features\n\nIf your queries rely heavily on CTEs, window functions, or complex JSONB operations, PostgreSQL provides a more capable query engine. \n\nMySQL's window function support arrived late (8.0) and remains less comprehensive. Its JSON support, while functional, lacks PostgreSQL's JSONB indexing capabilities.\n\n#### Extreme write throughput\n\nClassic 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.\n\nFor workloads with millions of writes per second, databases designed for write-heavy patterns like Cassandra or ScyllaDB handle the load more naturally.\n\n#### Native horizontal scaling\n\nMySQL scales vertically well and handles read scaling through replicas, but write scaling requires sharding. \n\nUnlike databases that handle sharding internally (CockroachDB, TiDB, Cassandra), MySQL sharding requires external coordination through tools like Vitess or application-level logic.\n\n#### Complex data types\n\nPostgreSQL'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.\n\n#### Sophisticated geospatial queries\n\nMySQL 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.\n\n### 1.3 Common Interview Systems Using MySQL\n\n\n| System | Why MySQL Works |\n|--------|-----------------|\n| Social Network (Facebook) | Read-heavy, mature replication |\n| E-commerce (Shopify) | ACID for orders, read replicas for catalog |\n| Content Management | Simple CRUD, easy scaling |\n| User Authentication | Reliable, ACID transactions |\n| URL Shortener | Simple schema, high read volume |\n| Chat Metadata | User profiles, relationships |\n\n\n# 2. Storage Engines: InnoDB vs MyISAM\n\nOne 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.\n\nThis 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.\n\n### 2.1 InnoDB (Default, Recommended)\n\nInnoDB 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.\n\n\n```mermaid\nflowchart TD\n subgraph InnoDB[\"InnoDB Architecture\"]\n BP[Buffer Pool]:::primary\n RL[Redo Log]:::orange\n UL[Undo Log]:::orange\n DT[Data + Indexes
Tablespace Files]:::green\n end\n\n Q[Queries]:::teal --> BP\n BP -->|Write| RL\n BP -->|Old row versions| UL\n BP -->|Checkpoint| DT\n RL -->|Recovery| DT\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n#### Key features\n\n- **ACID compliant**: Full transaction support with commit, rollback, and crash recovery\n- **Row-level locking**: High concurrency for mixed read/write workloads\n- **Foreign keys**: Referential integrity enforcement\n- **MVCC**: Multi-Version Concurrency Control for consistent reads without locking\n- **Crash recovery**: Automatic recovery from redo logs\n- **Clustered index**: Data stored in primary key order\n\n### 2.2 MyISAM (Legacy)\n\nMyISAM was the default before MySQL 5.5. Still used for specific cases.\n\n#### Key features\n\n- **Table-level locking**: Simple but limits concurrency\n- **Full-text search**: Native full-text indexing (InnoDB now has this too)\n- **Compressed tables**: Read-only compressed storage\n- **No transactions**: No ACID support\n- **No crash recovery**: Data corruption possible on crash\n\n### 2.3 When to Use Each\n\n\n| Factor | InnoDB | MyISAM |\n|--------|--------|--------|\n| Transactions | Yes | No |\n| Locking | Row-level | Table-level |\n| Foreign keys | Yes | No |\n| Crash recovery | Yes | No |\n| Full-text search | Yes (5.6+) | Yes |\n| Concurrency | High | Low |\n| Use case | Production systems | Read-only analytics, legacy |\n\n\n**Modern recommendation:** Use InnoDB for everything. MyISAM's advantages have been eliminated in recent MySQL versions.\n\n\n```sql\n-- Check table engine\nSHOW TABLE STATUS WHERE Name = 'users';\n\n-- Convert MyISAM to InnoDB\nALTER TABLE users ENGINE = InnoDB;\n\n-- Set default for new tables\nSET default_storage_engine = InnoDB;\n```\n\n\n---\n\n# 3. InnoDB Deep Dive\n\nInnoDB 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.\n\n### 3.1 Buffer Pool\n\nThe 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.\n\nIf not (a \"miss\"), it must read from disk, which is orders of magnitude slower.\n\n\n```mermaid\nflowchart LR\n subgraph BufferPool[\"Buffer Pool (RAM)\"]\n DP[Data Pages]:::primary\n IP[Index Pages]:::orange\n UL[Undo Logs]:::green\n AL[Adaptive Hash Index]:::teal\n end\n\n subgraph Disk[\"Disk Storage\"]\n TS[Tablespace Files]:::red\n end\n\n DP <-->|Read/Write| TS\n IP <-->|Read/Write| TS\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nBesides 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**.\n\nThe 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.\n\nWhat 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.\n\n### 3.2 Redo Log (Write-Ahead Log)\n\nHow can MySQL acknowledge a commit immediately while data pages are still only in memory? \n\nThe 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).\n\n\n```mermaid\nflowchart LR\n A[Transaction Commit]:::primary --> B[Write to Redo Log]:::orange\n B --> C[Acknowledge Client]:::green\n B --> D[Background Flush to Data Files]:::teal\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nRedo 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.\n\nThe 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.\n\nThe 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.\n\nInnoDB 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.\n\nIf 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.\n\n### 3.3 Undo Log and MVCC\n\nA 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.\n\nInnoDB 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.\n\n**How MVCC works:**\n\n\n```mermaid\nflowchart TD\n subgraph Transaction1[\"Transaction 1 (Read)\"]\n R1[SELECT * FROM users
WHERE id = 1]:::primary\n end\n\n subgraph Transaction2[\"Transaction 2 (Write)\"]\n W1[UPDATE users
SET name = 'Bob'
WHERE id = 1]:::orange\n end\n\n subgraph UndoLog[\"Undo Log\"]\n V1[Old Version:
name = 'Alice']:::green\n end\n\n subgraph DataPage[\"Data Page\"]\n V2[Current Version:
name = 'Bob']:::teal\n end\n\n R1 -->|Reads old version| V1\n W1 --> V2\n W1 -->|Stores old value| V1\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nReaders 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.\n\n### 3.4 Clustered Index\n\nOne 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.\n\n\n```mermaid\nflowchart TD\n subgraph ClusteredIndex[\"Clustered Index (Primary Key)\"]\n R[Root: 50]:::primary\n L1[10-49]:::orange\n L2[51-100]:::orange\n D1[Row 10: id=10, name=Alice, email=...]:::green\n D2[Row 25: id=25, name=Bob, email=...]:::green\n D3[Row 75: id=75, name=Carol, email=...]:::green\n end\n\n R --> L1\n R --> L2\n L1 --> D1\n L1 --> D2\n L2 --> D3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n#### Implications\n\n- Primary key lookups are fastest (data is right there)\n- Range scans on primary key are efficient (data is contiguous)\n- Secondary indexes store primary key, not row pointer\n- Choose primary key wisely (impacts all queries)\n\n**Primary key best practices:**\n\n\n| Approach | Pros | Cons |\n|----------|------|------|\n| Auto-increment INT | Compact, sequential inserts | Hotspot on last page |\n| UUID | No hotspot, globally unique | Large (16 bytes), random inserts |\n| Ordered UUID | Unique, sequential | Still 16 bytes |\n| Natural key | Meaningful | May change, often large |\n\n\n\n> **TIP**\n>\n> **Design note:** Primary key choice affects storage layout, not just uniqueness. Auto-increment integers are compact and insert mostly sequentially, which reduces page splits. Random UUIDs scatter inserts across the clustered index and can increase fragmentation.\n>\n> However, UUIDs may be necessary for distributed systems where generating sequential IDs requires coordination. Ordered UUIDs (like ULIDs) offer a middle ground: globally unique but roughly sequential.\n\n\n---\n\n# 4. Indexing Strategies\n\nThe 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.\n\nBut 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.\n\n### 4.1 B+Tree Index Structure\n\nMySQL 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.\n\n\n```mermaid\nflowchart LR\n subgraph BPlusTree[\"B+Tree Index\"]\n R[Root Node]:::primary\n I1[Internal Node]:::orange\n I2[Internal Node]:::orange\n L1[Leaf: 1,2,3]:::green\n L2[Leaf: 4,5,6]:::green\n L3[Leaf: 7,8,9]:::green\n L4[Leaf: 10,11,12]:::green\n end\n\n R --> I1\n R --> I2\n I1 --> L1\n I1 --> L2\n I2 --> L3\n I2 --> L4\n L1 -.->|Linked| L2\n L2 -.->|Linked| L3\n L3 -.->|Linked| L4\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n#### Key properties\n\n- Leaf nodes contain all values and are linked\n- Range scans follow leaf node links\n- O(log N) lookups\n- Efficient for equality and range queries\n\n### 4.2 Primary vs Secondary Indexes\n\nBecause 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. \n\nLooking 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.\n\n\n```mermaid\nflowchart LR\n subgraph Secondary[\"Secondary Index (email)\"]\n S1[alice...]:::orange --> PK1[PK: 10]:::teal\n S2[bob...]:::orange --> PK2[PK: 25]:::teal\n end\n\n subgraph Primary[\"Primary Index (id)\"]\n P1[id: 10 → Full Row]:::green\n P2[id: 25 → Full Row]:::green\n end\n\n PK1 --> P1\n PK2 --> P2\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nThis 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.\n\n### 4.3 Composite Indexes\n\nWhen queries filter on multiple columns, a composite index on those columns can be far more effective than separate indexes. But column order matters critically.\n\n\n```sql\n-- Create composite index\nCREATE INDEX idx_user_date ON orders(user_id, created_at);\n\n-- Uses index efficiently (leftmost prefix)\nSELECT * FROM orders WHERE user_id = 123;\nSELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';\nSELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;\n\n-- Does NOT use this index efficiently\nSELECT * FROM orders WHERE created_at > '2024-01-01'; -- Missing user_id\n```\n\n\nThink 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.\n\n**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.\n\n### 4.4 Covering Indexes\n\nInclude all columns needed by query to avoid table lookup.\n\n\n```sql\n-- Query needs: user_id, status, total\nSELECT user_id, status, total FROM orders WHERE user_id = 123;\n\n-- Regular index (requires table lookup)\nCREATE INDEX idx_user ON orders(user_id);\n\n-- Covering index (no table lookup needed)\nCREATE INDEX idx_user_covering ON orders(user_id, status, total);\n```\n\n\n**EXPLAIN shows \"Using index\" for covered queries:**\n\n\n```sql\nEXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 123;\n-- Extra: Using index (covering index used)\n```\n\n\n### 4.5 Index Hints and FORCE INDEX\n\nWhen MySQL chooses wrong index, you can hint:\n\n\n```sql\n-- Suggest an index\nSELECT * FROM orders USE INDEX (idx_user_date) WHERE user_id = 123;\n\n-- Force an index\nSELECT * FROM orders FORCE INDEX (idx_user_date) WHERE user_id = 123;\n\n-- Ignore an index\nSELECT * FROM orders IGNORE INDEX (idx_created) WHERE created_at > '2024-01-01';\n```\n\n\n#### Use sparingly\n\nHints 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.\n\n### 4.6 Full-Text Indexes\n\nMySQL 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.\n\nOnce 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.\n\n\n```sql\n-- Create full-text index\nCREATE FULLTEXT INDEX idx_content ON articles(title, body);\n\n-- Search\nSELECT * FROM articles\nWHERE MATCH(title, body) AGAINST('mysql performance' IN NATURAL LANGUAGE MODE);\n\n-- Boolean mode for more control\nSELECT * FROM articles\nWHERE MATCH(title, body) AGAINST('+mysql -postgresql' IN BOOLEAN MODE);\n```\n\n\n### 4.7 Index Selection Guidelines\n\nChoosing the right index approach comes down to understanding your query patterns:\n\n\n```mermaid\nflowchart TD\n Q[What does your query need?]:::primary\n Q --> E{Filter on one column?}:::yellow\n E -->|Equality| SI[Single column index]:::green\n E -->|Range| SI\n\n Q --> M{Filter on multiple columns?}:::yellow\n M -->|Yes| CI[Composite index
Order: equality cols first,
then range cols]:::orange\n\n Q --> S{Need to avoid table lookup?}:::yellow\n S -->|Yes| CV[Covering index
Include SELECT columns]:::teal\n\n Q --> F{Full-text search?}:::yellow\n F -->|Yes| FT[FULLTEXT index]:::teal\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef yellow fill:#ffd43b,stroke:#000,color:#000\n```\n\n\n\n| Query Pattern | Index Strategy |\n|---------------|----------------|\n| Equality (WHERE a = ?) | Index on (a) |\n| Equality + Range (a = ? AND b > ?) | Index on (a, b) |\n| Multiple equalities (a = ? AND b = ?) | Index on (a, b) or (b, a) |\n| ORDER BY | Include sort columns in index |\n| GROUP BY | Index on grouped columns |\n| JOIN | Index on join columns |\n| SELECT specific columns | Covering index |\n\n\n### 4.8 Indexing Anti-Patterns\n\n\n| Anti-Pattern | Problem | Solution |\n|--------------|---------|----------|\n| Too many indexes | Slow writes | Remove unused indexes |\n| Indexes on low-cardinality | Not selective | Use for high-cardinality |\n| Function on indexed column | Index not used | Rewrite query |\n| Leading wildcard LIKE | Full scan | Full-text search |\n| Over-indexing | Maintenance overhead | Index for actual queries |\n\n\n\n```sql\n-- Bad: Function on indexed column\nSELECT * FROM users WHERE YEAR(created_at) = 2024;\n\n-- Good: Range query uses index\nSELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';\n\n-- Bad: Leading wildcard\nSELECT * FROM users WHERE name LIKE '%john%';\n\n-- Good: Prefix match uses index\nSELECT * FROM users WHERE name LIKE 'john%';\n```\n\n\n\n> **TIP**\n>\n> **Design note:** Indexes should follow query patterns. For \"get all orders for a user, sorted by date,\" create an index on `(user_id, created_at)`. If the query only selects `order_id` and `status`, include those fields to make the index covering and avoid the extra table lookup.\n>\n> Indexing is targeted optimization, not a generic checkbox.\n\n\n---\n\n# 5. Transactions and Locking\n\nConcurrency 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.\n\nInnoDB 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.\n\n### 5.1 Isolation Levels\n\nIsolation 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.\n\n#### Common anomalies\n\n- **Dirty read**: reading uncommitted changes from another transaction\n- **Non-repeatable read**: the same query returns different results within the same transaction\n- **Phantom read**: new rows appear that match a previously-run query\n\n\n```mermaid\nflowchart TD\n subgraph Anomalies[\"Isolation Anomalies\"]\n D1[Dirty Read]:::red\n D2[Non-Repeatable Read]:::orange\n D3[Phantom Read]:::teal\n end\n\n D1 --- E1[\"Reading uncommitted changes
from another transaction\"]:::secondary\n D2 --- E2[\"Same query returns different
values within a transaction\"]:::secondary\n D3 --- E3[\"New rows appear that match
a previously-run query\"]:::secondary\n\n classDef red fill:#ff8787,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef secondary fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n\n| Level | Dirty Read | Non-Repeatable Read | Phantom Read |\n|-------|------------|---------------------|--------------|\n| READ UNCOMMITTED | Yes | Yes | Yes |\n| READ COMMITTED | No | Yes | Yes |\n| REPEATABLE READ (default) | No | No | Yes* |\n| SERIALIZABLE | No | No | No |\n\n\n- InnoDB's REPEATABLE READ avoids phantoms two different ways. Plain (non-locking) `SELECT`s read from a consistent MVCC snapshot taken at the start of the transaction, so new rows committed by others are simply invisible. Locking reads (`SELECT ... FOR UPDATE`, `FOR SHARE`) and writes instead rely on **gap / next-key locks** on the scanned range to block the conflicting inserts.\n\n\n```sql\n-- Set isolation level for session\nSET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;\n\n-- Set for single transaction\nSTART TRANSACTION;\nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n-- ... queries ...\nCOMMIT;\n```\n\n\n### 5.2 InnoDB Locking Types\n\nInnoDB doesn't only lock \"rows.\" It can also lock **gaps between rows** to prevent conflicting inserts.\n\n\n```mermaid\nflowchart LR\n subgraph Locks[\"InnoDB Lock Types\"]\n RL[Record Lock
Lock single row]:::primary\n GL[Gap Lock
Lock gap between records]:::orange\n NKL[Next-Key Lock
Record + Gap]:::green\n TL[Table Lock
Entire table]:::red\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\n**Record Lock:** Locks a single index record.\n\n\n```sql\n-- Record lock on id = 10\nSELECT * FROM users WHERE id = 10 FOR UPDATE;\n```\n\n\n**Gap Lock:** Locks the gap between index records. Prevents inserts in the range.\n\n\n```sql\n-- Gap lock prevents inserts between existing records\nSELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;\n-- Other transactions cannot insert id = 15\n```\n\n\n**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.\n\n### 5.3 Locking Reads\n\nMySQL gives you explicit control over whether reads should acquire locks.\n\n\n```sql\n-- Shared lock (others can read, not write)\nSELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;\n-- or in MySQL 8.0+\nSELECT * FROM accounts WHERE id = 1 FOR SHARE;\n\n-- Exclusive locking read (blocks competing updates/locking reads;\n-- plain consistent SELECTs can still read an older committed version)\nSELECT * FROM accounts WHERE id = 1 FOR UPDATE;\n\n-- Skip locked rows (MySQL 8.0+, useful for job queues)\nSELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;\n\n-- Non-blocking (MySQL 8.0+, return immediately if locked)\nSELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;\n```\n\n\nThese are foundational tools for building correct systems: inventory deduction, job processing, seat booking, wallet transfers, and more.\n\n### 5.4 Deadlock Handling\n\nA **deadlock** happens when two transactions each hold locks the other needs, so neither can progress.\n\nInnoDB 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`.\n\nTo inspect deadlocks:\n\n\n```mermaid\nflowchart LR\n subgraph Deadlock[\"Deadlock Scenario\"]\n T1[Transaction 1
Holds lock on A
Wants lock on B]:::primary\n T2[Transaction 2
Holds lock on B
Wants lock on A]:::orange\n end\n\n T1 -->|Waiting| T2\n T2 -->|Waiting| T1\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n#### How to reduce deadlocks in practice\n\n- access tables/rows in a consistent order across transactions\n- keep transactions short (do less work between BEGIN and COMMIT)\n- use the lowest isolation level that still preserves correctness\n- add indexes so locks cover fewer rows (smaller lock footprint)\n\n\n```sql\n-- Check recent deadlocks\nSHOW ENGINE INNODB STATUS;\n-- Look for \"LATEST DETECTED DEADLOCK\" section\n```\n\n\n### 5.5 Optimistic vs Pessimistic Locking\n\nThere are two broad approaches to handling write conflicts.\n\n**Pessimistic Locking:** Lock the row first, then update it.\n\n\n```sql\nBEGIN;\nSELECT * FROM inventory WHERE product_id = 123 FOR UPDATE;\n-- Row is locked\nUPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;\nCOMMIT;\n```\n\n\n**Optimistic Locking:** Allow concurrent reads, then detect conflicts at update time using a version column.\n\n\n```sql\n-- Read with version\nSELECT id, quantity, version FROM inventory WHERE product_id = 123;\n-- Returns: quantity = 10, version = 5\n\n-- Update with version check\nUPDATE inventory\nSET quantity = 9, version = 6\nWHERE product_id = 123 AND version = 5;\n\n-- If 0 rows affected, another transaction updated first - retry\n```\n\n\n\n| Approach | Best For | Trade-off |\n|----------|----------|-----------|\n| Pessimistic | High contention | Blocks other transactions |\n| Optimistic | Low contention | Retry overhead on conflict |\n\n\nA 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).\n\n\n> **TIP**\n>\n> **Design note:** Choose pessimistic or optimistic locking based on conflict frequency. For inventory deduction in a flash sale, `SELECT FOR UPDATE` prevents overselling by serializing competing updates to the same row.\n>\n> For user profile updates where conflicts are rare, optimistic locking reduces database blocking at the cost of occasional retry overhead when conflicts do occur. Both approaches are valid; the context determines which is better.\n\n\n---\n\n# 6. Replication Architectures\n\nA 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.\n\nMySQL replication is mature, widely operated, and well supported by tooling. The important design choice is which replication mode matches the durability and latency requirements.\n\n### 6.1 Replication Basics\n\nMySQL 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.\n\n\n```mermaid\nflowchart TD\n subgraph Source[\"Source (Primary)\"]\n S[MySQL Primary]:::primary\n BL[Binary Log]:::orange\n end\n\n subgraph Replicas[\"Replicas\"]\n R1[Replica 1]:::green\n R2[Replica 2]:::green\n R3[Replica 3]:::green\n end\n\n C[Clients]:::teal -->|Writes| S\n S --> BL\n BL -->|Replicate| R1\n BL -->|Replicate| R2\n BL -->|Replicate| R3\n C -->|Reads| R1\n C -->|Reads| R2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n#### Replication process\n\nThe flow looks like this:\n\n1. **Source writes** data and appends the change to the **binlog**\n2. Replicas stream binlog events from the source\n3. Replicas persist those events locally\n4. Replicas apply the events to their own data\n\n### 6.2 Replication Choices to Explain\n\nThe choices worth explaining are:\n\n\n| Choice | Why it matters |\n|--------|----------------|\n| Row-based replication | Avoids many correctness issues from replaying non-deterministic SQL statements |\n| Asynchronous replication | Best write latency, but recent writes can be lost if the primary fails before replicas receive them |\n| Semi-synchronous replication | Reduces data-loss risk by waiting for at least one replica acknowledgment, at the cost of write latency |\n| Group Replication | Adds automatic failover and consensus-style coordination, but increases complexity and write cost |\n\n\nMost 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.\n\n### 6.3 Replication Lag\n\nReplication 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.\n\nCommon strategies to deal with lag:\n\n- **Read-your-writes:** route a user's reads to the primary briefly after a write\n- **Causal consistency:** track binlog position and wait until a replica catches up\n- **Accept staleness:** many feeds, analytics, and dashboards tolerate small delays\n\n\n> **TIP**\n>\n> **Design note:** Replication configuration depends on the failure mode you can tolerate. For a typical web application prioritizing throughput, asynchronous replication with row-based format is common, but a primary crash can lose the latest transactions that have not reached a replica.\n>\n> For systems where losing any committed transaction is unacceptable, semi-synchronous replication ensures at least one replica has received the data before acknowledging the commit. For automatic failover without human intervention, Group Replication provides consensus-based high availability at the cost of write latency.\n\n\n### 6.4 Failover\n\nReplication 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.\n\nA failover, whether triggered manually or automatically, goes through the same phases:\n\n1. **Detect** the primary is unreachable. This is usually health checks plus a timeout, tuned to avoid promoting on a brief network blip.\n2. **Choose** the most up-to-date replica as the new primary. The best candidate is the replica that has applied the most of the old primary's binlog.\n3. **Promote** that replica: stop its replication, make it writable, and point the remaining replicas at it as their new source.\n4. **Redirect** traffic so the application writes to the new primary, typically by updating the proxy or a service-discovery record rather than changing application config.\n\n\n```mermaid\nflowchart TD\n P[Old Primary
fails]:::red --> D[Detect failure
health check + timeout]:::orange\n D --> C[Choose most
caught-up replica]:::primary\n C --> PR[Promote replica
make writable]:::green\n PR --> RP[Repoint other
replicas to new primary]:::teal\n PR --> RT[Redirect writes
via proxy]:::teal\n\n classDef red fill:#ff8787,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n#### GTIDs make promotion safe\n\nThe 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.\n\nAny 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.\n\n#### Data loss depends on the replication mode\n\nHow much data the failover loses ties directly back to the replication choice from 6.2:\n\n- **Asynchronous:** the primary acknowledges commits before replicas receive them. If it dies with un-replicated transactions in its binlog, those commits are lost. Failover is fast but not lossless.\n- **Semi-synchronous:** the primary waits for at least one replica to acknowledge receipt before acknowledging the client. As long as that replica is the one promoted, no acknowledged commit is lost.\n- **Group Replication / InnoDB Cluster:** a group of nodes agrees on commits through a consensus protocol and promotes a new primary automatically, without an external tool.\n\n#### Avoiding split-brain\n\nThe 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**.\n\nProduction 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.\n\n#### Who orchestrates it\n\nIn 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.\n\nThe 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.\n\n---\n\n# 7. Sharding Strategies\n\nVertical 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.\n\nWhen a single primary becomes your bottleneck, **sharding** is the next step.\n\nSharding 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.\n\n### 7.1 Why Shard?\n\nThe 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.\n\n\n```mermaid\nflowchart TD\n\n subgraph After[\"After Sharding\"]\n SH1[(Shard 1
250GB)]:::green\n SH2[(Shard 2
250GB)]:::green\n SH3[(Shard 3
250GB)]:::green\n SH4[(Shard 4
250GB)]:::green\n end\n\t\n subgraph Before[\"Before Sharding\"]\n S1[(Single MySQL
1TB, 10K QPS)]:::red\n end\t\n\n classDef red fill:#ff8787,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nSharding is typically justified when at least one of these becomes true:\n\n- your dataset no longer fits comfortably on a single server (storage + indexes + working set)\n- write throughput is beyond what one primary can sustain\n- read replicas still can't keep up (or replication lag becomes unacceptable)\n- you need geographic distribution (data residency, latency, regional isolation)\n\n### 7.2 Sharding Approaches\n\n#### Range-Based Sharding\n\nSplit by contiguous key ranges\n\n- Shard 1: `user_id` 1 to 1000000\n- Shard 2: `user_id` 1000001 to 2000000\n- Shard 3: `user_id` 2000001 to 3000000\n\n\n### Pros\n\n- easy to understand and debug\n- efficient range queries *within* a shard\n- adding a new shard is conceptually simple (new range)\n\n### Cons\n\n- uneven distribution is common (some ranges are hotter than others)\n- hotspots can form (e.g., \"recent\" IDs get most traffic)\n- rebalancing ranges is operationally painful\n\n\n#### Hash-Based Sharding\n\nChoose shard by hashing the key\n\n\n```shell\nShard = hash(user_id) % num_shards\n\nuser_id 123 → hash(123) % 4 = 2 → Shard 2\nuser_id 456 → hash(456) % 4 = 0 → Shard 0\n```\n\n\n\n### Pros\n\n- typically even distribution\n- avoids \"newest ID is hottest\" hotspots\n- simple routing logic\n\n### Cons\n\n- range queries across shards become expensive\n- adding shards often requires reshuffling keys (unless you use consistent hashing / indirection)\n- cross-shard joins are painful\n\n\n#### Directory-Based Sharding\n\nUse a lookup service/table to map keys to shards\n\n\n```shell\nLookup service maps key → shard\nuser_id 123 → Lookup → Shard 3\n```\n\n\n\n### Pros\n\n- flexible mapping (you can move one user/tenant at a time)\n- easier rebalancing without rehashing everything\n- supports custom routing rules (VIP tenants, geo placement)\n\n### Cons\n\n- the lookup layer becomes critical infrastructure (needs HA + caching)\n- extra latency on cache misses\n- more moving parts and more failure modes\n\n\n### 7.3 Choosing a Shard Key\n\nThe shard key determines almost everything: distribution, routing, and query shape. A bad shard key can make sharding worse than a single database.\n\n#### Good shard key properties\n\n- High cardinality (many distinct values)\n- Even distribution\n- Frequently used in queries\n- Rarely changes\n\n**Common shard keys:**\n\n\n| Entity | Good Shard Key | Why |\n|--------|---------------|-----|\n| Users | user_id | Even distribution, used in most queries |\n| Orders | user_id (not order_id) | Keeps user's orders together |\n| Messages | conversation_id | Messages grouped by conversation |\n| Multi-tenant | tenant_id | Isolates tenant data |\n\n\nA practical heuristic: shard by the key that your application most naturally routes by (\"who owns this data?\").\n\n### 7.4 Cross-Shard Operations\n\nSharding is easy when queries are \"single-key, single-shard.\" It gets expensive when queries need data from many shards.\n\n\n```sql\n-- Single shard (fast)\nSELECT * FROM orders WHERE user_id = 123;\n\n-- Cross-shard (requires scatter-gather)\nSELECT * FROM orders WHERE created_at > '2024-01-01';\n-- Must query ALL shards and merge results\n```\n\n\n#### Strategies\n\n- Design schema to minimize cross-shard queries\n- Denormalize data to avoid joins\n- Use global tables for small, read-heavy data\n- Accept eventual consistency for aggregations\n\n### 7.5 Vitess for MySQL Sharding\n\n**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.\n\n\n```mermaid\nflowchart TD\n A[Application]:::primary --> VT[VTGate
Query Router]:::orange\n\n subgraph Shards[\"Sharded MySQL\"]\n S1[Shard -80]:::green\n S2[Shard 80-]:::green\n end\n\n VT --> VTT1[VTTablet]:::teal --> S1\n VT --> VTT2[VTTablet]:::teal --> S2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n#### Vitess provides\n\n- Automatic query routing\n- Connection pooling\n- Schema management\n- Resharding without downtime\n- Used by YouTube, Slack, Square\n\nIt'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.\n\n### 7.6 When NOT to Shard\n\nSharding is powerful, but it's a \"you own the complexity forever\" decision. Exhaust simpler levers first:\n\n1. **Optimize queries** - Add indexes, rewrite queries\n2. **Vertical scaling** - Bigger server, more RAM\n3. **Read replicas** - Scale reads without sharding\n4. **Caching** - Redis for hot data\n5. **Archive old data** - Move cold data to separate storage\n\nShard only when the single-primary architecture is the hard blocker and you're confident the access patterns justify the added complexity.\n\n\n> **TIP**\n>\n> **Design note:** Sharding should come after simpler fixes: query tuning, better indexes, read replicas, and caching hot data. Once data is split across shards, cross-shard queries, migrations, and operational recovery all become harder.\n>\n> If sharding becomes necessary, the shard key choice is critical: it should align with your primary access patterns so that most queries hit a single shard. Cross-shard queries and transactions are expensive and should be exceptional, not normal.\n\n\n---\n\n# 8. Query Optimization\n\nWhen 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.\n\n### 8.1 EXPLAIN and EXPLAIN ANALYZE\n\nYou can't optimize what you don't understand. Start by inspecting how MySQL plans to execute your query.\n\n- **EXPLAIN** shows the plan: join order, chosen indexes, and estimated rows.\n- **EXPLAIN ANALYZE** (MySQL 8.0.18+) runs the query and reports **actual execution timing**, which is far more reliable than estimates when you're debugging real slowdowns.\n\n\n```sql\n-- Basic explain\nEXPLAIN SELECT * FROM orders WHERE user_id = 123;\n\n-- With actual execution statistics (MySQL 8.0.18+)\nEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;\n```\n\n\n#### Key EXPLAIN columns\n\n\n| Column | Meaning |\n|--------|---------|\n| type | Join type (const, ref, range, index, ALL) |\n| possible_keys | Indexes that could be used |\n| key | Index actually used |\n| rows | Estimated rows to examine |\n| filtered | Percentage of rows filtered by condition |\n| Extra | Additional information |\n\n\n#### Type values (best to worst)\n\n\n| Type | Meaning | Performance |\n|------|---------|-------------|\n| const | Single row by primary key | Excellent |\n| eq_ref | One row per join | Excellent |\n| ref | Multiple rows by index | Good |\n| range | Index range scan | Good |\n| index | Full index scan | Medium |\n| ALL | Full table scan | Poor |\n\n\nAs a first pass: **avoid **`ALL`** on large tables**, and be wary when `rows` is huge.\n\n### 8.2 Common Query Optimizations\n\n#### Use covering indexes\n\nIf 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`.\n\n#### Avoid `SELECT *`\n\n`SELECT *` increases I/O and prevents some optimizations, especially when rows are wide.\n\n\n```sql\n-- Bad: Fetches all columns\nSELECT * FROM orders WHERE user_id = 123;\n\n-- Good: Only needed columns\nSELECT id, status, total FROM orders WHERE user_id = 123;\n```\n\n\n#### Make ORDER BY + LIMIT index-friendly\n\nIf MySQL can walk an index in the needed order, it can stop early instead of sorting a large dataset.\n\n\n```sql\n-- Bad: Sorts all rows then limits\nSELECT * FROM orders ORDER BY created_at DESC LIMIT 10;\n\n-- Good: Index on created_at allows early termination\nCREATE INDEX idx_created ON orders(created_at DESC);\n```\n\n\n#### Batch operations\n\nReduce round trips and transaction overhead.\n\n\n```sql\n-- Bad: Many round trips\nINSERT INTO events VALUES (1, 'click');\nINSERT INTO events VALUES (2, 'view');\nINSERT INTO events VALUES (3, 'click');\n\n-- Good: Single round trip\nINSERT INTO events VALUES (1, 'click'), (2, 'view'), (3, 'click');\n```\n\n\n### 8.3 Query Patterns to Avoid\n\nBeyond the indexing anti-patterns from 4.8 (functions on indexed columns and leading wildcards), two more patterns quietly defeat indexes:\n\n\n```sql\n-- Implicit type conversion (defeats the index on phone)\nSELECT * FROM users WHERE phone = 1234567890; -- phone is VARCHAR\n-- Correct type lets MySQL use the index\nSELECT * FROM users WHERE phone = '1234567890';\n\n-- OR across different columns can force a scan\nSELECT * FROM orders WHERE user_id = 123 OR status = 'pending';\n-- Splitting into a UNION lets each branch use its own index\nSELECT * FROM orders WHERE user_id = 123\nUNION\nSELECT * FROM orders WHERE status = 'pending';\n```\n\n\nThe 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.\n\nThe `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.\n\n### 8.4 Pagination Optimization\n\nOffset pagination looks simple, but it gets slower as offsets grow because MySQL still has to walk past skipped rows.\n\n\n```sql\n-- Bad: Must scan 10000 rows to skip them\nSELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10000;\n\n-- Good: Keyset/cursor pagination\nSELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 10;\n```\n\n\nFor stable ordering with ties (common in feeds), use a composite cursor:\n\n\n```sql\n-- Store last seen values\n-- Previous page ended at: created_at = '2024-01-15 10:00:00', id = 12345\n\nSELECT * FROM orders\nWHERE (created_at, id) < ('2024-01-15 10:00:00', 12345)\nORDER BY created_at DESC, id DESC\nLIMIT 20;\n```\n\n\n### 8.5 Optimization Checklist\n\nOptimization works best as a repeatable loop rather than a grab-bag of server commands:\n\n1. Identify the queries that matter most by frequency, latency, or total time.\n2. Use `EXPLAIN` or `EXPLAIN ANALYZE` to see whether the query scans too many rows, misses an index, sorts too much data, or joins in a bad order.\n3. Fix the access pattern: add or adjust indexes, rewrite the query, avoid unnecessary columns, or move deep pagination to keyset pagination.\n4. Verify the new plan and watch for write overhead from extra indexes.\n\nThe important signal is that you can connect a slow user-facing path to the query plan and choose a focused fix.\n\n---\n\n# 9. MySQL vs Other Databases\n\nUse this comparison to keep the database choice grounded in requirements:\n\n\n| Alternative | Prefer MySQL when... | Prefer the alternative when... |\n|-------------|----------------------|--------------------------------|\n| PostgreSQL | The workload is simple, read-heavy, and operational familiarity matters most | You need richer SQL, JSONB indexing, extensions, or geospatial features |\n| MongoDB | The data is structured, relational, and transaction-heavy | The data is document-shaped and the schema changes frequently |\n| Cassandra | You need SQL flexibility and ACID transactions at moderate scale | Writes are extreme, access patterns are fixed, and availability dominates |\n| TiDB | One write primary is enough and simpler operations matter | You need MySQL compatibility with distributed SQL and horizontal write scale |\n\n\n---\n\n# Summary\n\nUse MySQL for read-heavy relational applications where operational maturity and predictable behavior matter more than advanced SQL features or native distributed writes.\n\nAnchor 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.\n\n---\n\n# Quiz","pageType":"system-design-interviews"}

Get Premium