MySQL Interview Questions and Answers: Indexes, InnoDB vs MyISAM, Transactions, Optimization, and Design

MyISAM and InnoDB Index Differences

MyISAM uses non‑clustered B+Tree indexes where leaf nodes store record pointers, while InnoDB stores the full row in the leaf nodes of a clustered index; auxiliary indexes in InnoDB require a second lookup via the primary key.

Why InnoDB Uses Auto‑Increment IDs as Primary Keys

Auto‑increment keys keep inserts sequential, reducing page splits and fragmentation compared to random keys such as IDs based on personal identifiers.

Sharding (Database and Table Partitioning)

Sharding reduces single‑node load and improves query performance. Strategies include vertical (splitting rarely used columns) and horizontal (modulo or time‑based) partitioning, with considerations for data migration, pagination, and global unique IDs.

Clustered vs Non‑Clustered Indexes

Clustered indexes store data rows in leaf nodes, matching table order; non‑clustered indexes store pointers to rows, requiring an extra lookup.

ACID Properties

Atomicity

: All operations succeed or all are rolled back. Consistency: Data integrity rules are preserved before and after a transaction. Isolation: Prevents interference between concurrent transactions (levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable). Durability: Committed changes survive system failures.

Transaction Isolation Levels and Concurrency Issues

MySQL defaults to Repeatable Read. Issues include dirty reads, non‑repeatable reads, and phantom reads; higher isolation reduces anomalies but impacts performance.

InnoDB vs MyISAM Storage Engines

InnoDB supports transactions, row‑level locking, and foreign keys; MyISAM offers table‑level locking, no transactions, and is suited for read‑heavy workloads.

Database Normalization (3NF)

Ensures atomic columns, single‑entity tables, and direct primary‑key dependencies; benefits include reduced redundancy, while drawbacks involve more joins.

Index Usage Tips

Avoid functions or arithmetic on indexed columns.

Prefer NOT NULL and high‑cardinality columns.

Use composite indexes following the left‑most prefix rule.

Beware of range queries that disable indexing on subsequent columns.

Avoid !=, NOT IN, and excessive OR conditions.

Leverage covering indexes and consider implicit type conversion effects.

Data Types: CHAR vs VARCHAR

CHAR is fixed‑length (padded with spaces) and faster for short strings; VARCHAR is variable‑length, saving space for longer values.

NOW() vs CURRENT_DATE()

NOW()

returns date and time; CURRENT_DATE() returns only the date.

Performance Optimization Strategies

Create appropriate indexes and avoid SELECT *.

Apply vertical/horizontal partitioning.

Use read/write splitting and caching (e.g., Memcached).

Write efficient SQL and prefer prepared statements.

Locking: Pessimistic vs Optimistic

Pessimistic locking uses SELECT ... FOR UPDATE to acquire row locks; optimistic locking relies on version/timestamp checks (e.g., CAS) without holding locks.

Why MySQL Uses B+Tree Indexes

B+Tree stores all keys in leaf nodes with sequential pointers, enabling efficient range scans and lower I/O compared to hash, binary, or red‑black trees.

Index Creation Considerations

Index NOT NULL columns.

Place high‑cardinality columns first in composite indexes.

Keep indexed columns as small as possible.

DROP, DELETE, TRUNCATE Differences

DELETE removes rows (can be rolled back, fires triggers); TRUNCATE removes all rows quickly (no rollback, no triggers); DROP deletes the table definition entirely.

Views and Cursors

Views are virtual tables; cursors allow row‑by‑row processing when set‑based operations are unsuitable.

Stored Procedures

Pre‑compiled SQL blocks that improve performance; invoked via CALL statements or from application code.

Join Types

Inner join: only matching rows.

Left/Right outer join: all rows from one side plus matches.

Full outer join: all rows from both sides.

Cross join: Cartesian product.

Hand‑written SQL Practice

Emphasizes frequent practice of SELECT statements, especially those involving multiple table joins.