Quiz: SQL Indexes: B-Tree, Composite Indexes, EXPLAIN and Why an Index Is Not Used

Total: 7 questions

1. 

What is an SQL index, why do you need it, and what do you pay for it?

An index is a separate, ordered structure that stores the values of one or more columns together with references to the rows, so the database can find matching rows without scanning the whole table. In InnoDB almost every index is a B-tree (B+tree).

  • Without an index MySQL checks every row (a full table scan, type: ALL in EXPLAIN).
  • With an index it walks down the tree to the values it needs: WHERE, joins and sorting get faster — ORDER BY column LIMIT 10 takes the first 10 values from the index without Using filesort.
  • The price is disk space (a copy of the indexed columns) and slower writes: every INSERT and DELETE updates all indexes of the table, and an UPDATE of an indexed column moves the entry inside the index.

An index and a key are not the same: a key sets a rule (uniqueness, a reference), an index speeds up searching. MySQL builds an index for every PRIMARY KEY, UNIQUE and FOREIGN KEY on its own, while an ordinary index from CREATE INDEX forbids nothing.

2. 

How does a B-tree index work in InnoDB, and how is a clustered index different from a secondary one?

A B-tree is a balanced tree: values are sorted, all leaves are at the same depth, and a lookup goes from the root to a leaf in O(log n) page reads. Neighbouring leaves are linked, so ranges and sorted output are read sequentially. InnoDB uses the B+tree variant: data lives only in the leaves. A page is 16 KB, so even for tens of millions of rows the tree is usually 3–4 levels deep.

  • The clustered index is the PRIMARY KEY: its leaves hold complete rows, which makes a lookup by id the fastest. Without a primary key InnoDB uses the first UNIQUE index on NOT NULL columns, and failing that, a hidden 6-byte row ID.
  • A secondary index stores its column values and the primary key value. A lookup takes two steps: find the id values in the secondary index, then go down the clustered index for each id.

Two consequences follow. A long primary key (a UUID in CHAR(36)) bloats every secondary index. And if a query needs only the columns of a secondary index (including id), the second step is skipped — that is a covering index, shown as Extra: Using index in EXPLAIN.

3. 

How do you create, drop and list indexes in MySQL?

CREATE INDEX idx_topics_title ON topics (title);
ALTER TABLE topics ADD INDEX idx_topics_title (title);   -- the same with ALTER TABLE
CREATE UNIQUE INDEX uq_topics_title ON topics (title);   -- unique
CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at);
DROP INDEX idx_topics_title ON topics;                   -- or ALTER TABLE ... DROP INDEX
SHOW INDEX FROM messages;
  • An index can also be declared in CREATE TABLE: INDEX idx_topics_title (title); KEY is a synonym for INDEX.
  • The name is unique within the table: a repeat gives ERROR 1061: Duplicate key name. A unique index is not created if the column already has duplicates (ERROR 1062).
  • TEXT and BLOB cannot be indexed in full: ERROR 1170: BLOB/TEXT column 'body' used in key specification without a key length. Give a prefix length — (body(50)).
  • SHOW INDEX prints one row per column of every index: Non_unique = 0 means unique, Cardinality estimates the number of distinct values, Index_type is BTREE.

MySQL creates the indexes for PRIMARY KEY, UNIQUE and foreign keys by itself.

4. 

What is a composite index and the leftmost prefix rule? In what order should the columns go?

A composite index is built on several columns: entries are ordered by the first column, then by the second when the first is equal, and so on. So an index on (a, b, c) works for conditions on a, on a and b, on all three, but not on b or c without a. This is the leftmost prefix rule, like a phone book sorted by last name, then first name: every Smith is easy to find, everyone named John is not. The order of conditions in WHERE does not matter; the column order in the index does.

  • Equality first, range second. For topic_id = 2 AND created_at >= '2024-03-01', an index on (topic_id, created_at) reads one slice (key_len 9), while (created_at, topic_id) grabs every thread after 1 March (key_len 5). Columns after a range are not used for the lookup.
  • One index for WHERE and ORDER BY. WHERE topic_id = 2 ORDER BY created_at DESC LIMIT 20 gives Using filesort with an index on (topic_id) and Backward index scan with (topic_id, created_at): the rows are already ordered, and MySQL stops after 20.
5. 

How do you read MySQL EXPLAIN: what do type, key, key_len, rows and Extra mean?

EXPLAIN SELECT ... shows the plan without executing the query.

  • type from best to worst: const (one row by a primary or unique key), eq_ref, ref (all rows with a value of a non-unique index), range (a range of the index), index (the whole index), ALL (the whole table).
  • possible_keys lists the suitable indexes, key is the chosen one. A non-empty possible_keys with key: NULL means the index exists but the optimizer decided to read the table.
  • key_len is the key bytes used: INT NOT NULL 4, nullable INT 5, DATETIME 5. It tells you how many columns of a composite index are in use.
  • rows and filtered are estimates from statistics, not exact numbers.
  • Extra: Using index — covering index, Using index condition — the condition is checked on index entries, Using filesort — a separate sort, Using temporary — a temporary table.

EXPLAIN ANALYZE (MySQL 8.0.18+) does run the query and shows the real time and row counts next to the estimates.

6. 

Why is MySQL not using an index even though it exists?

An index is useless when the condition cannot be turned into a search over ordered values. EXPLAIN then shows key: NULL or type: ALL.

  • A function on the column: DATE(created_at) = '2024-03-01'. Rewrite it as the range created_at >= '2024-03-01' AND created_at < '2024-03-02' or create a functional index.
  • A leading %: LIKE '%fishing%'; LIKE 'Ice%' does use the index.
  • A string column compared with a number: phone = 14155552671 — Note 1739 in SHOW WARNINGS. Pass a string in the stored format: '+14155552671'.
  • OR across different columns — needs an index on each (index merge) or a UNION.
  • Leftmost prefix broken: author_id = 3 with an index on (topic_id, author_id).
  • Low selectivity: author_id = 1 is a third of the table, and a sequential scan is cheaper than trips into the clustered index. Not a bug.
  • !=, NOT IN, and different character sets or collations in a JOIN.
7. 

When is an index useless or harmful, and how do you safely drop an extra index?

An index is useless on small tables (a lookup table of a dozen rows fits on one page), on columns nobody searches by, and on columns with two or three distinct values. It hurts where data changes often: every extra index slows down INSERT, UPDATE and DELETE. A view counter in an index is deleted and re-inserted at a new position on every UPDATE.

Duplicate index: (a) is redundant when (a, b) exists — the composite index serves the same queries by the leftmost prefix. The reverse does not hold, and (b, a) is a different index. The foreign key index fk_messages_topic (topic_id) can be dropped once (topic_id, created_at) exists; without it you get ERROR 1553: Cannot drop index ... needed in a foreign key constraint.

On a production database, make the index invisible first: ALTER TABLE messages ALTER INDEX name INVISIBLE; (MySQL 8.0). The optimizer stops using it, but it is still maintained, and if a query slows down, visibility comes back instantly.

Page 1 of 1