100 MySQL Interview Questions: Indexes, Transactions, and Storage Engines Explained

Preface

This article is aimed at developers and focuses on MySQL knowledge points that are frequently asked in interviews, such as indexes, transactions, optimization, and related concepts.

Indexes

1. What is an index? An index is a data structure that helps quickly locate rows.

2. What data structures are used for indexes? The implementation depends on the storage engine; common types in MySQL are hash indexes and B‑tree indexes, with InnoDB using B‑tree by default.

3. Differences between hash and B‑tree indexes

Hash indexes are fast for equality queries but cannot support range queries, sorting, or fuzzy matching, and they always require a table lookup. B‑tree indexes maintain ordered nodes, support range queries, sorting, and can sometimes avoid a table lookup when covering indexes are used.

Hash indexes are faster for equality lookups but do not support range queries.

Hash indexes cannot be used for ordering.

Hash indexes cannot perform fuzzy or multi‑column left‑most‑prefix matches.

Hash indexes always involve a table lookup; B‑tree can avoid it with covering or clustered indexes.

Hash indexes may suffer from collisions and unstable performance.

4. What is a clustered index? In a B‑tree index, leaf nodes may store the full row (clustered index) or only the key (non‑clustered). In InnoDB the primary key is the clustered index; if no primary key exists, InnoDB chooses a unique NOT NULL key or creates an implicit one.

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

Example: select age from employee where age < 20 can be satisfied by the index leaf nodes without a table lookup.

6. Factors to consider when creating indexes Frequency of use, suitability as query conditions, column order for composite indexes, and the impact on write performance.

7. What is a composite index and why does order matter? A composite index combines multiple columns; the index can be used only if the query predicates follow the defined column order.

8. How to check if an index is used? Use EXPLAIN to view the execution plan, which shows possible_key, key, and key_len fields.

9. Situations where an existing index is ignored

Inequality predicates

Columns involved in arithmetic or functions

LIKE patterns with a leading wildcard

When a full table scan is cheaper

Range query 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 satisfy ACID properties: all succeed or all fail.

2. What does ACID stand for?

Atomicity – all‑or‑nothing execution.

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

Isolation – uncommitted changes are invisible to other transactions.

Durability – committed changes survive crashes.

3. Problems caused by concurrent transactions

Dirty read

Non‑repeatable read

Phantom read

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; may still have phantom reads.

SERIALIZABLE – eliminates all three problems but reduces concurrency.

5. Default isolation level of InnoDB REPEATABLE READ.

6. What are locks in MySQL? Mechanisms to control concurrent access. Shared (read) locks allow multiple readers; exclusive (write) locks allow only one writer.

7. Types of locks

Shared lock (read lock)

Exclusive lock (write lock)

Lock granularity depends on the storage engine: InnoDB implements row‑level, page‑level, and table‑level locks, with row‑level being the finest.

Table Design

1. Why define a primary key? Guarantees uniqueness and improves performance of DML operations.

2. Auto‑increment ID vs UUID Auto‑increment IDs are recommended because they keep the clustered index ordered, avoiding fragmentation and insert overhead.

3. Why use NOT NULL? NULL columns require extra storage and can cause unexpected behavior.

4. Which column type to store password hashes? Use CHAR for fixed‑length strings to save space and improve lookup speed.

Storage Engines

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

2. Differences between InnoDB and MyISAM

InnoDB supports transactions; MyISAM does not.

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

InnoDB supports MVCC; MyISAM does not.

InnoDB supports foreign keys; MyISAM does not.

MyISAM supports full‑text indexes; InnoDB historically did not.

Miscellaneous Questions

1. CHAR vs VARCHAR CHAR is fixed‑length, VARCHAR is variable‑length; CHAR is faster for known‑size data.

2. Meaning of VARCHAR(10) and INT(10) VARCHAR(10) limits the maximum length; INT(10) only affects display width.

3. Binary log formats STATEMENT, ROW, and MIXED, each with trade‑offs between size and fidelity.

4. Handling huge pagination Optimize queries to avoid loading large offsets, use index‑covering queries, or redesign the UI to avoid deep page jumps; caching can also help.

5. How to approach slow‑query analysis Identify missing indexes, unnecessary column loads, or large data volumes; then rewrite queries, add indexes, or consider sharding.

6. Horizontal vs vertical sharding examples

Horizontal: split a large user table into multiple tables by ID range.

Vertical: separate large text columns (e.g., article content) into a different table.

7. What are stored procedures and their pros/cons? Pre‑compiled SQL blocks that can improve performance and reduce network traffic, but they are harder to maintain and less portable in fast‑moving web projects.

8. Three normal forms 1NF – atomic columns; 2NF – non‑key columns fully depend on the whole primary key; 3NF – non‑key columns depend only on the primary key.

9. Difference between # and $ in MyBatis # treats the value as a parameter (prevents SQL injection); $ directly concatenates the value into the SQL string.