Quiz: SQL Indexes: B-Tree, Composite Indexes, EXPLAIN and Why an Index Is Not Used
Study the lesson first: 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?
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: ALLinEXPLAIN). - With an index it walks down the tree to the values it needs:
WHERE, joins and sorting get faster —ORDER BY column LIMIT 10takes the first 10 values from the index withoutUsing filesort. - The price is disk space (a copy of the indexed columns) and slower writes: every
INSERTandDELETEupdates all indexes of the table, and anUPDATEof 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?
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 byidthe fastest. Without a primary key InnoDB uses the firstUNIQUEindex onNOT NULLcolumns, 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
idvalues in the secondary index, then go down the clustered index for eachid.
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?
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);KEYis a synonym forINDEX. - 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). TEXTandBLOBcannot 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 INDEXprints one row per column of every index:Non_unique = 0means unique,Cardinalityestimates the number of distinct values,Index_typeisBTREE.
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?
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_len9), while(created_at, topic_id)grabs every thread after 1 March (key_len5). 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 20givesUsing filesortwith an index on(topic_id)andBackward index scanwith(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?
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.
typefrom 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_keyslists the suitable indexes,keyis the chosen one. A non-emptypossible_keyswithkey: NULLmeans the index exists but the optimizer decided to read the table.key_lenis the key bytes used:INT NOT NULL4, nullableINT5,DATETIME5. It tells you how many columns of a composite index are in use.rowsandfilteredare 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?
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 rangecreated_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 1739inSHOW WARNINGS. Pass a string in the stored format:'+14155552671'. ORacross different columns — needs an index on each (index merge) or aUNION.- Leftmost prefix broken:
author_id = 3with an index on(topic_id, author_id). - Low selectivity:
author_id = 1is 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 aJOIN.
7. When is an index useless or harmful, and how do you safely drop an extra index?
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.