Master MySQL Indexes and Locks: Boost Performance and Avoid Pitfalls

Index Overview

Previously I thought I knew everything about indexes: they speed up retrieval, they add overhead to INSERT/UPDATE/DELETE, they consume storage, they follow the left‑most prefix rule, and MySQL supports clustered and non‑clustered indexes as well as hash and B‑tree indexes.

However, interview questions can expose gaps in understanding.

1. Basics of Indexes

MySQL stores data in pages, which form a doubly linked list, while records within a page form a singly linked list. Each page has a directory that allows binary search to locate a record slot quickly when using the primary key.

When searching by a non‑primary column, MySQL must scan the linked list sequentially.

For a query like SELECT * FROM user WHERE username='Java3y', MySQL locates the page, traverses the double‑linked list to the page, then scans the single‑linked list within the page, which is slow for large data sets.

2. How Indexes Speed Up Retrieval

Indexes transform unordered data into an ordered structure (typically a B‑tree), allowing fast location of rows via a directory.

Finding a row with id=8 becomes a quick B‑tree lookup rather than a full scan.

3. How Indexes Slow Down DML

Maintaining a balanced B‑tree requires extra work during INSERT, UPDATE, or DELETE, which reduces write performance.

4. Hash Indexes

Hash indexes compute a hash value from the key and locate the row in a single step, offering very fast lookups but with limitations: they cannot be used for sorting, do not support the left‑most prefix rule, suffer from collisions with many duplicate keys, and cannot handle range queries.

5. Does InnoDB Support Hash Indexes?

InnoDB primarily uses adaptive hash indexes that are created automatically by the storage engine; users cannot manually create hash indexes.

6. Clustered vs. Non‑Clustered Indexes

Clustered index: created on the primary key; leaf nodes store the actual table rows.

Non‑clustered index: created on non‑primary columns; leaf nodes store the primary key and indexed columns, requiring a lookup (back‑table) to retrieve full rows.

Non‑clustered indexes can be multi‑column and may generate multiple index trees, consuming disk space.

7. Covering Indexes

If the indexed columns contain all columns needed by the query, the engine can satisfy the query directly from the index without a back‑table lookup.

8. Left‑most Prefix Rule

An index on (a, b, c, d) can be used for queries that specify a, a & b, or a & b & c with equality conditions; range conditions stop further matching.

9. Automatic Optimization of = and IN Order

MySQL reorders equality and IN conditions to match the index column order for optimal use.

Index Summary

Follow the left‑most prefix rule.

Choose high‑cardinality columns (COUNT(DISTINCT col)/COUNT(*)).

Avoid functions on indexed columns.

Prefer extending existing indexes over creating new ones.

Remember MySQL can use only one index per query.

Lock Overview

MySQL locks include exclusive (X), shared (S), intention locks, row locks, table locks, gap locks, optimistic and pessimistic locks, and deadlocks. Their behavior depends on the storage engine and transaction isolation level.

1. Why Learn Locks

Understanding locks helps in scenarios requiring explicit locking, improves program control, and prepares you for interviews.

2. Table Locks

Table locks are cheap, fast, but block concurrency.

Row locks are more expensive but allow higher concurrency.

InnoDB supports both row and table locks; MyISAM only supports table locks.

3. Optimistic vs. Pessimistic Locks

Pessimistic lock: use SELECT ... FOR UPDATE to acquire an exclusive row lock.

Optimistic lock: add a version column; update only if the version matches, otherwise reject.

4. Gap (GAP) Locks

When a range query is executed under REPEATABLE READ, InnoDB locks the gaps between index entries to prevent phantom reads.

5. Deadlocks

Deadlocks can be mitigated by accessing tables in a consistent order, breaking large transactions, acquiring all needed locks early, lowering isolation level, and adding appropriate indexes.

Lock Summary

MyISAM adds table locks automatically; InnoDB adds row locks when indexes are used, otherwise table locks.

InnoDB provides shared (S) and exclusive (X) locks, and supports MVCC for non‑blocking reads.

Repeatable READ with gap locks prevents phantom reads.