100 MySQL Interview Q&A: Indexes, Transactions, and Schema Design Essentials

Index Basics

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

Hash indexes store keys in a hash table, providing fast equality lookups but no range queries, sorting, or fuzzy matching. B‑tree indexes are balanced multi‑way trees that support range queries, sorting, and left‑most‑prefix matching.

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

Hash indexes require a table lookup (back‑table) to retrieve full rows.

Hash indexes may suffer from collisions, leading to unpredictable performance.

In most cases, B‑tree indexes offer stable and reliable performance.

Clustered indexes store the full row in the leaf nodes. 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.

Non‑clustered indexes may require a back‑table lookup unless the query can be satisfied entirely by the index (covering index).

When creating indexes, consider field usage frequency, query patterns, and index order for composite indexes.

Composite Indexes

Composite (multi‑column) indexes require queries to use the indexed columns in the defined order. The index is ordered first by the leftmost column, then the next, and so on. To maximize usage, place the most selective or frequently queried columns first.

Transaction Fundamentals

A transaction is a sequence of operations that must satisfy ACID properties: Atomicity, Consistency, Isolation, and Durability.

Isolation levels in MySQL:

READ UNCOMMITTED – allows dirty reads.

READ COMMITTED – prevents dirty reads but may cause non‑repeatable reads.

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

SERIALIZABLE – eliminates all concurrency anomalies but greatly reduces throughput.

InnoDB’s default isolation level is REPEATABLE READ.

Locking Mechanisms

MySQL uses shared (read) locks and exclusive (write) locks. InnoDB implements row‑level, page‑level, and table‑level locks, with row‑level locks offering the finest granularity and highest concurrency.

Table Design Guidelines

Define a primary key for every table, preferably an auto‑increment integer rather than a UUID, to avoid fragmentation and maintain insertion performance.

Use CHAR for fixed‑length fields such as password hashes or IDs, and VARCHAR for variable‑length data.

Set columns to NOT NULL to save storage space and avoid unexpected NULL handling.

Storage Engine Overview

MySQL supports multiple storage engines; InnoDB is the default and recommended for most use 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 supports foreign keys; MyISAM does not.

MyISAM supports full‑text indexes; InnoDB does not (though newer versions add limited support).

Miscellaneous Topics

Data Types : CHAR is fixed‑length, VARCHAR is variable‑length; INT(10) only affects display width, not storage size.

Binlog Formats : statement, row, and mixed. Statement logs record SQL statements, row logs record changed rows, and mixed uses the appropriate format per statement.

Large Pagination : Avoid offset‑based scans on huge tables. Prefer index‑covering queries or use “> last_id” pagination. Cache results when possible.

Query Optimization : Analyze execution plans, ensure index usage, rewrite queries to avoid unnecessary columns or rows, and consider sharding (horizontal) or column splitting (vertical) for very large tables.

Stored Procedures : Provide pre‑compiled SQL blocks that can improve performance and reduce network traffic, but may hinder rapid development and version control in fast‑moving projects.

Normalization : Follow the first three normal forms to avoid data anomalies, unless performance considerations justify denormalization.

MyBatis Parameter Syntax : # safely binds values as parameters (prevents SQL injection), while $ performs literal string substitution.