100 Essential MySQL Interview Questions: Indexes, Transactions, and More

Introduction

The article targets developers and presents a collection of 100 MySQL interview questions, aiming to deepen understanding of key concepts such as indexes, transactions, optimization, and table design.

Index Related

Indexes are data structures that accelerate data lookup. Their implementation depends on the storage engine; InnoDB primarily uses B+‑tree indexes, while hash indexes are also available.

Key differences between hash and B+‑tree indexes:

Hash indexes are faster for equality queries but cannot handle range queries.

Hash indexes do not support ordering, fuzzy matching, or left‑most prefix of multi‑column indexes.

Hash indexes always require a lookup to the base table (back‑table), whereas B+‑tree indexes can serve queries directly when conditions like clustered or covering indexes are met.

Hash indexes may suffer from collisions, leading to unpredictable performance; B+‑tree performance is more stable.

In most cases, B+‑tree indexes provide stable and good query speed and are the default choice.

Clustered index (primary key) details: In InnoDB, the primary key is the clustered index. If no primary key is defined, InnoDB picks the first unique NOT NULL index or creates an implicit one.

Other index considerations:

When creating an index, consider field usage frequency and query patterns.

For composite (joint) indexes, the order of columns matters; the most selective or frequently queried columns should appear first.

Use EXPLAIN to check whether a query uses an index (fields like possible_keys, key, key_len).

Indexes may be ignored in cases such as inequality queries, functions on columns, leading wildcards in LIKE, full‑table scans deemed cheaper, or when a preceding range condition prevents use of later columns in a composite index.

Transaction Related

A transaction is a sequence of operations that must satisfy ACID properties:

Atomicity : all operations succeed or all fail.

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

Isolation : uncommitted changes are invisible to other transactions (with some exceptions).

Durability : once committed, changes survive crashes.

Concurrent transactions can cause:

Dirty reads

Non‑repeatable reads

Phantom reads

MySQL provides four 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 – highest isolation, eliminates all three phenomena but reduces concurrency.

InnoDB’s default isolation level is REPEATABLE READ.

MySQL uses two lock types:

Shared (read) locks allow concurrent reads.

Exclusive (write) locks block other reads and writes.

Lock granularity varies by engine: InnoDB supports row‑level, page‑level, and table‑level locks, with increasing overhead.

Table Structure Design

Define a primary key for every table; a surrogate auto‑increment integer is preferred over UUID because it keeps the clustered index ordered and avoids fragmentation.

Use CHAR for fixed‑length strings (e.g., password hashes) and VARCHAR for variable‑length data.

Columns that must not be NULL should be declared NOT NULL to save space and avoid unexpected null handling.

Storage Engine Related

MySQL supports multiple storage engines; InnoDB is the default and recommended for most cases.

Key differences between InnoDB and 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 it does).

Miscellaneous Questions

VARCHAR vs CHAR : CHAR is fixed‑length and faster for known‑size data; VARCHAR is variable‑length and saves space for unpredictable sizes.

INT(10) meaning : The number in parentheses affects display width, not storage size.

Binlog formats : STATEMENT, ROW, and MIXED, each with trade‑offs between size and fidelity.

Large pagination : Optimize by using covering indexes, limiting by primary key ranges, or caching results; avoid deep offsets.

Slow query optimization : Identify missing indexes, unnecessary column loads, or large tables; consider query rewrite, index tuning, or sharding.

Horizontal vs vertical sharding : Horizontal splits rows across tables (e.g., by ID range); vertical splits columns into separate tables (e.g., moving large text columns).

Stored procedures : Pre‑compiled SQL blocks that can improve performance but are often discouraged in fast‑moving web projects.

Normalization : Follow 1NF, 2NF, 3NF to avoid redundancy; sometimes denormalize for performance with justification.