Master MySQL: 100 Essential Q&A on Indexes, Transactions, and Design

Preface

The article targets developers and assumes familiarity with MySQL basics, focusing on interview‑style questions rather than exhaustive tutorials on SELECT syntax or server deployment.

Indexes

1. What is an index? An index is a data structure that speeds up data lookup.

2. What data structures are used for indexes? Implementation depends on the storage engine; InnoDB commonly uses B‑tree indexes, while hash indexes also exist.

3. Differences between hash and B‑tree indexes

Hash indexes excel at equality queries but cannot handle range queries, sorting, or fuzzy matching.

B‑tree indexes support range queries, sorting, and left‑most‑prefix matching, offering stable performance.

4. What is a clustered index? In InnoDB, the primary key is the clustered index; leaf nodes store the full row data.

5. Do non‑clustered indexes always require a table lookup? Not necessarily; if the query can be satisfied entirely by the index (covering index), no additional lookup is needed.

6. Factors to consider when creating indexes Frequency of column use in WHERE clauses, order of columns in composite indexes, and the impact on write performance.

7. What is a composite (union) index and why does order matter? A composite index can be used only if the query predicates follow the left‑most column order; placing the most selective columns first improves index utilization.

8. How to determine if an index is used? Use EXPLAIN to view the execution plan, checking fields like possible_keys, key, and key_len.

9. Situations where an index is ignored

Non‑equality predicates (e.g., !=)

Functions or arithmetic on indexed columns

Leading wildcard in LIKE patterns (e.g., '%abc')

MySQL decides a full table scan is cheaper

Range condition on the first column of a composite index prevents use of later columns

Transactions

1. What is a transaction? A sequence of operations that must either all succeed or all fail, adhering to ACID properties.

2. ACID explained

Atomicity – all‑or‑nothing execution.

Consistency – the database moves from one valid state to another.

Isolation – intermediate results are invisible to other transactions.

Durability – committed changes survive crashes.

3. Concurrency problems with multiple transactions

Dirty read – a transaction reads uncommitted data from another.

Non‑repeatable read – the same query returns different results within a transaction.

Phantom read – new rows appear in a range query after another transaction inserts them.

4. MySQL isolation levels

READ UNCOMMITTED – allows dirty reads.

READ COMMITTED – prevents dirty reads but allows non‑repeatable reads.

REPEATABLE READ – prevents dirty and non‑repeatable reads; still vulnerable to phantom reads.

SERIALIZABLE – eliminates all three phenomena but greatly reduces concurrency.

5. Default isolation level for InnoDB InnoDB uses REPEATABLE READ by default.

6. MySQL locking mechanisms Locks ensure consistent access under concurrency. Shared (read) locks allow multiple readers; exclusive (write) locks allow only one writer. InnoDB implements row‑level, page‑level, and table‑level locks, with row‑level offering the finest granularity.

Table Design

1. Why define a primary key? Guarantees row uniqueness and enables efficient lookups; a surrogate auto‑incrementing ID is recommended when no natural key exists.

2. Auto‑increment ID vs. UUID as primary key Auto‑increment IDs are preferred because they preserve insert order, reduce fragmentation, and improve B‑tree performance; UUIDs cause random inserts and higher overhead.

3. Why declare columns NOT NULL? NULL columns require extra storage bits and can lead to unexpected behavior; defining NOT NULL saves space and simplifies logic.

4. Storing password hashes Use fixed‑length CHAR columns (e.g., CHAR(60)) instead of VARCHAR for hashes, salts, or ID numbers to save space and improve lookup speed.

Storage Engines

1. Supported MySQL storage engines InnoDB, MyISAM, Memory, Archive, etc. InnoDB is the default and generally the best choice.

2. InnoDB vs. MyISAM

InnoDB supports transactions; MyISAM does not.

InnoDB provides row‑level locking; MyISAM uses table‑level locking.

InnoDB implements MVCC; MyISAM does not.

InnoDB enforces foreign keys; MyISAM does not.

MyISAM supports full‑text indexes; InnoDB historically did not (now supported in newer versions).

Miscellaneous Topics

1. CHAR vs. VARCHAR CHAR is fixed‑length and faster for known‑size data (e.g., password hashes); VARCHAR is variable‑length and saves space for unpredictable lengths.

2. Meaning of VARCHAR(10) and INT(10) VARCHAR(10) limits the maximum stored characters to 10; INT(10) only affects display width, not storage size.

3. Binlog formats Three formats exist: STATEMENT (records SQL statements), ROW (records row changes), and MIXED (uses STATEMENT when safe, otherwise ROW).

4. Handling huge pagination Reduce data loaded by using indexed ranges, subqueries, or ID‑based pagination; alternatively, cache results or limit user navigation to predictable pages.

5. Slow‑query analysis and optimization Identify missing indexes, unnecessary column loads, or large data volumes; rewrite queries, add appropriate indexes, or consider sharding (horizontal) and vertical partitioning.

6. Horizontal vs. vertical sharding examples

Horizontal: Split a large user table into multiple tables based on ID ranges or suffixes.

Vertical: Separate rarely used large columns (e.g., article content) into a different table, keeping frequently accessed columns in the primary table.

7. Stored procedures Pre‑compiled SQL blocks that can improve performance and reduce network traffic but are less flexible for rapid development and harder to maintain in fast‑changing web projects.

8. Normal forms (1NF, 2NF, 3NF) 1NF prohibits repeating groups; 2NF requires non‑key attributes to depend on the whole primary key; 3NF eliminates transitive dependencies.

9. MyBatis # vs. $ syntax # creates a prepared‑statement placeholder, protecting against SQL injection; $ performs literal string substitution, which can be unsafe.