Senior & Lead Engineer
Interview Mastery Hub

Why B+Tree Needs Leftmost Prefix Rule

In an InnoDB B+Tree, index keys are sorted lexicographically based on the order of columns defined in the index: (ColA, ColB, ColC).

• If a query filters on ColB without ColA, the B+Tree root cannot branch because ColB is only sorted within duplicate values of ColA.
• Range Queries Stop Index Filtering: Once a range operator (>, <, BETWEEN, >=) is evaluated, subsequent columns in the composite index cannot be used for B+Tree traversal.

Fixing the Slow Query

Because created_at used a range operator (>=), placing it first broke index lookups for status and user_id.

Optimal Composite Index Column Order:

1. Exact equality matches with highest cardinality first: user_id
2. Equality match: status
3. Range match last: created_at

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

Query execution time drops from 8.5 seconds (full scan) to 0.8 milliseconds (index seek).

The Three InnoDB Row Lock Types

1. Record Lock: Locks the physical index record itself (e.g. WHERE id = 5 FOR UPDATE).
2. Gap Lock: Locks the gap between index records (or before the first / after the last) to prevent other transactions from inserting new rows in that gap.
3. Next-Key Lock: A combination of a Record Lock on the index record plus a Gap Lock on the gap preceding it.

Why MySQL Uses Next-Key Locks by Default

Under the default REPEATABLE READ isolation level, MySQL uses Next-Key locking to solve the Phantom Read problem (preventing another transaction from inserting a new row that matches your query's range before you commit).

Reducing Lock Contention:

Switching transaction isolation to READ COMMITTED (with binlog_format=ROW) disables Gap Locks except for foreign key constraint checks and duplicate key checks.

Why LIMIT 5000000, 20 is Slow

MySQL must scan, read from disk, and discard all 5,000,000 preceding rows before returning the 20 requested records.

Solution 1: Cursor-Based Pagination (Seek Method) - FASTEST

Instead of offset, filter by the last seen ID:

SELECT * FROM audit_logs
WHERE id < 45000000
ORDER BY id DESC
LIMIT 20;

Direct B+Tree seek, runs in 0.4 milliseconds on any page number.

Solution 2: Deferred Join (Late Row Lookup)

If random page offset jumps are required, use a covering index subquery:

SELECT a.* FROM audit_logs a
JOIN (
    SELECT id FROM audit_logs
    ORDER BY created_at DESC
    LIMIT 5000000, 20
) AS sub ON a.id = sub.id;

The inner subquery scans only the memory-efficient index tree without fetching heavy row columns until the final 20 IDs are found.

Diagnosing EXPLAIN ANALYZE

• Using filesort: MySQL cannot use an index to satisfy the ORDER BY clause and must perform an extra sorting pass in memory (sort_buffer_size) or on disk.
• Using temporary: MySQL creates an internal temporary table (in RAM or disk) to hold intermediate results, common with GROUP BY and DISTINCT on non-indexed columns.

Remediation:

1. Create a composite index covering both the filter and the order:

   CREATE INDEX idx_merchant_created ON orders (merchant_id, created_at DESC);
   

2. When query runs WHERE merchant_id = 5 ORDER BY created_at DESC, MySQL reads pre-sorted index nodes directly without filesort or temporary tables.

Range Partitioning by Date

Partitioning splits a single logical table into separate physical files on disk:

ALTER TABLE transactions PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202601 VALUES LESS THAN (202602),
    PARTITION p202602 VALUES LESS THAN (202603),
    PARTITION p202603 VALUES LESS THAN (202604),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Benefits:

1. Partition Pruning: Queries with WHERE created_at BETWEEN '2026-02-01' AND '2026-02-28' scan ONLY the p202602 physical file.
2. Instant Purges: Dropping 50 million historical rows takes 0.1 seconds via ALTER TABLE transactions DROP PARTITION p202401; rather than a catastrophic DELETE FROM transactions... holding millions of row locks.

Sizing innodb_buffer_pool_size

On a dedicated database server, set innodb_buffer_pool_size to 70% - 80% of total system RAM:

• For 128GB RAM: innodb_buffer_pool_size = 96G
• Configure multiple buffer pool instances to reduce mutex contention: innodb_buffer_pool_instances = 8

Key Fixes for Replication Lag

1. Enable Multi-Threaded Slaves (MTS):

   slave_parallel_workers = 16
   slave_parallel_type = LOGICAL_CLOCK
   

2. Eliminate Huge Single-Query Writes: Break massive 5-million-row DELETE or UPDATE statements into chunks of 5,000.
3. Disable Sync Binlog on Replicas: Set sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2 on read replicas.

Clustered vs Secondary Index

• Clustered Index: The physical table data rows are stored directly inside the leaf nodes of the Primary Key B+Tree.
• Secondary Index: Leaf nodes store only the indexed column value and the Primary Key value (not a physical row pointer).
• To retrieve non-indexed columns, MySQL must perform a Bookmark Lookup (reading secondary index -> extracting PK -> searching Clustered Index).

Covering Index (Using index)

When all columns requested in SELECT, WHERE, and ORDER BY exist within the secondary index itself, MySQL retrieves data directly from the index in RAM without touching table row data on disk.

CREATE INDEX idx_users_org_email_date ON users (org_id, email, created_at);

Query: SELECT email, created_at FROM users WHERE org_id = 10; is 100% satisfied by the index.

ACID Isolation Levels

Analyzing LATEST DETECTED DEADLOCK

Run:

SHOW ENGINE INNODB STATUS\G

Look for:

• (1) TRANSACTION vs (2) TRANSACTION
• LOCK WAIT statements
• RECORD LOCKS space id ... lock_mode X
• Identify which transaction was chosen as the deadlock victim and rolled back based on the lowest number of modified rows.

Why InnoDB Cannot Store an Exact Row Count

Because InnoDB uses MVCC (Multi-Version Concurrency Control), different concurrent transactions see different numbers of visible rows at the exact same moment.

• MyISAM locked tables and stored a single counter, but did not support transactions.
• Solution for Large Tables: Use Redis cached counters or query information_schema.TABLES.TABLE_ROWS for approximations.

Functional Index on JSON Expressions

ALTER TABLE orders ADD INDEX idx_customer_code ((CAST(details->>'$.customer_code' AS CHAR(30))));

MySQL 8 allows indexing expressions directly without needing stored physical columns.

innodb_flush_log_at_trx_commit Settings

• 1 (Default / Strict ACID): Redo log is flushed to disk on every single transaction commit. Maximum safety, high disk IOPS.
• 2: Redo log is written to OS cache on commit and flushed to disk once per second. If OS crashes, at most 1 second of data is lost, but write throughput increases up to 10x.
• 0: Flushed once per second in memory.

Best Practice for High-Scale Systems

Avoid heavy database-level cascading deletes. Instead, perform soft-deletes or chunked background queue deletions:

-- Delete in chunks of 5,000 to prevent long-held row locks
DELETE FROM order_items WHERE order_id = 12345 LIMIT 5000;

Redo Log vs Undo Log

• Redo Log (Write-Ahead Logging / WAL): Records physical changes to data pages. During crash recovery, MySQL replays the redo log to restore committed transactions that were not yet flushed from RAM to table data files.
• Undo Log: Records the opposite actions needed to rollback uncommitted transactions active at the time of the crash.

Recursive CTE Example

WITH RECURSIVE OrgChart AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart ORDER BY level;

MySQL FULLTEXT Index with N-Gram Parser

ALTER TABLE products ADD FULLTEXT INDEX ft_title_desc (title, description) WITH PARSER ngram;

-- High performance full text search
SELECT * FROM products
WHERE MATCH(title, description) AGAINST('+laptop +pro' IN BOOLEAN MODE);

MySQL 8 Invisible Indexes & Online Drop

1. Make Index Invisible First:

   ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE;
   

   Verifies if optimizer or queries degrade without that index.
2. Drop Online:

   ALTER TABLE orders DROP INDEX idx_old_status, ALGORITHM=INPLACE, LOCK=NONE;
   

MySQL InnoDB Cluster Architecture

1. Group Replication: Uses a Paxos-based consensus protocol where transactions are validated across majority nodes (quorum) before commit.
2. MySQL Router: Automatically routes incoming read/write traffic to the primary node and read traffic across secondaries.
3. Automatic Failover: If the primary crashes, the cluster elects a new primary in seconds without human intervention.