B-Tree Indexes, Composite Keys, Prefix Rules, Covering Indexes, and Write Costs

Learning outcomes

ServiceHub has grown from a few thousand work orders to millions. A dashboard that once returned instantly now sometimes scans hundreds of thousands of rows, while write latency has also increased because the table accumulated several overlapping indexes. The correct response is not “add indexes until the query is fast.” An index is a persistent access path that trades storage and write work for faster reads, and the optimizer is free to reject it when its estimated cost is worse than another plan.

This lesson builds a precise InnoDB B-tree mental model: the primary key is the clustered index that organizes table records, each secondary index stores its own key plus the primary-key value needed to find the base record, and composite key order controls which predicates and sort orders can efficiently navigate the tree. Every design choice therefore affects both read access and write amplification.

1. Start from the query shape, not from an index name

DROP DATABASE IF EXISTS servicehub_index_lab;CREATE DATABASE servicehub_index_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;USE servicehub_index_lab;CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  customer_id BIGINT UNSIGNED NOT NULL,  status VARCHAR(16) NOT NULL,  priority TINYINT UNSIGNED NOT NULL,  region_code CHAR(3) NOT NULL,  scheduled_at DATETIME NOT NULL,  summary VARCHAR(240) NOT NULL,  details TEXT NULL,  total_cents INT UNSIGNED NOT NULL,  PRIMARY KEY (work_order_id)) ENGINE=InnoDB;INSERT INTO work_orders(customer_id,status,priority,region_code,scheduled_at,summary,details,total_cents)VALUES (101,'open',1,'BAK','2026-08-20 09:00:00','Router packet loss','Packet loss after firmware change',12000), (102,'open',2,'BAK','2026-08-20 10:00:00','Switch replacement','Access switch intermittently reboots',25000), (103,'closed',3,'TBZ','2026-08-19 15:30:00','UPS battery replacement','Battery health below threshold',18000), (104,'queued',2,'BAK','2026-08-21 08:00:00','Fiber attenuation','High attenuation on uplink',42000), (105,'open',1,'GAN','2026-08-20 11:30:00','VPN authentication','Users report failed MFA challenge',9000), (106,'closed',4,'BAK','2026-08-18 14:00:00','Cable cleanup','Rack cable remediation complete',7000);

Suppose dispatchers repeatedly ask for the next open work orders in one region, ordered by schedule. The query has three distinct access requirements: equality on status and region_code, a time-order requirement on scheduled_at, and a stable tie breaker on work_order_id. A good candidate index mirrors that shape instead of indexing every column independently.

CREATE INDEX idx_dispatch_queueON work_orders(status, region_code, scheduled_at, work_order_id);EXPLAINSELECT work_order_id, customer_id, scheduled_at, priorityFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at, work_order_idLIMIT 20;ANALYZE FORMAT=JSONSELECT work_order_id, customer_id, scheduled_at, priorityFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at, work_order_idLIMIT 20;

EXPLAIN tells you the optimizer’s chosen access type, possible keys, selected key, key length and estimated rows. ANALYZE FORMAT=JSON actually executes the statement and augments the plan with runtime fields such as r_rows, r_loops and timing information. That distinction matters: estimates diagnose what the optimizer believed; runtime counters show what happened in this execution.

2. Leftmost prefix is a navigation rule, not a slogan

The leftmost-prefix rule describes how the ordered key can be navigated. It does not mean that a prefix will always be selected. If status='open' matches most rows, a table scan can be cheaper than bouncing through a secondary index and then fetching base rows. Selectivity, data distribution, statistics, projection width and cache state all participate in the decision.

EXPLAIN SELECT * FROM work_orders WHERE status='open';EXPLAIN SELECT * FROM work_orders WHERE region_code='BAK';EXPLAIN SELECT * FROM work_ordersWHERE status='open' AND region_code='BAK'  AND scheduled_at >= '2026-08-20 00:00:00';

3. Covering reads save base-row lookups—but widen the index

A secondary index can sometimes satisfy a query using only values stored in the index leaf records. MariaDB may report Using index in the plan’s Extra column. This is commonly called a covering access path. Covering can reduce random base-row lookups, but there is no free lunch: every extra indexed column consumes pages, buffer-pool space and write maintenance.

EXPLAINSELECT work_order_id, scheduled_atFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at, work_order_id;EXPLAINSELECT work_order_id, scheduled_at, detailsFROM work_ordersWHERE status='open' AND region_code='BAK'ORDER BY scheduled_at, work_order_id;

The first query’s selected columns are represented by the secondary index key plus its InnoDB primary-key payload. The second asks for details, which is not in that index and can require base-row access. Do not respond by adding a large TEXT column to every index. Measure whether the avoided row lookups justify the larger write and cache footprint.

4. Prefix indexes trade comparison precision for smaller keys

MariaDB lets you index a prefix of a string column, for example the first 24 characters of summary. This can reduce key size for long strings, but the index distinguishes only the stored prefix. Many rows sharing that prefix can still require residual comparisons, and a prefix index cannot cover the full unindexed suffix.

CREATE INDEX idx_summary_prefix ON work_orders(summary(24));SHOW INDEX FROM work_orders;EXPLAINSELECT work_order_id, summaryFROM work_ordersWHERE summary LIKE 'Router packet%';

Prefix length should come from actual value distribution, collation rules, maximum index-key limits and workload measurements. A fashionable constant such as “index the first 20 characters” is not a design rule. Use COUNT(DISTINCT LEFT(summary,n)) at several candidate lengths to understand how much discrimination the prefix retains on your real data.

5. Deliberately wrong: create one index per filter column

CREATE INDEX idx_status_only ON work_orders(status);CREATE INDEX idx_region_only ON work_orders(region_code);CREATE INDEX idx_scheduled_only ON work_orders(scheduled_at);CREATE INDEX idx_priority_only ON work_orders(priority);SHOW INDEX FROM work_orders;

This is not automatically better than one workload-aligned composite index. Every insert now updates more B-trees; indexed-column updates can touch several trees; backup size and cache pressure increase; and redundant indexes complicate plan selection. MariaDB can use index-merge strategies in some cases, but index merge does not erase the cost of maintaining a weak portfolio.

Repair the design by inventorying real queries, testing candidate composites, and removing redundancy only after evidence. MariaDB’s ignored-index facility, available in modern releases, can be useful in staging: ALTER TABLE work_orders ALTER INDEX idx_status_only IGNORED keeps the index maintained but prevents normal optimizer use, so you can test impact before dropping it. Re-enable with NOT IGNORED.

6. Measure write/storage amplification without inventing performance numbers

SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_index_lab' AND TABLE_NAME='work_orders';SHOW SESSION STATUS LIKE 'Handler_write';SHOW SESSION STATUS LIKE 'Handler_update';SHOW INDEX FROM work_orders;

These observations do not directly convert into “milliseconds per index.” They establish what indexes exist, how much index space the table currently uses, and how many handler-level writes/updates occurred in the measured session. For a real benchmark, run the same controlled workload with the same data volume, cache warmup, durability settings and concurrency before and after the index change. Report the local conditions with the result.

7. Production judgment, verification, and cleanup

1. Write down the exact predicate, join, order and projection requirements before proposing an index.
2. Inspect SHOW INDEX and remove accidental duplicates from the hypothesis set.
3. Use EXPLAIN for estimates and ANALYZE FORMAT=JSON for executed runtime evidence.
4. Check whether the proposed key supports the intended leftmost prefix and ordering.
5. Measure write/storage consequences under a controlled workload.
6. Retain the lab for Lesson 2; later clean up with DROP DATABASE servicehub_index_lab;.

Next, the chapter handles the cases where the useful search key is not a stored base column at all: normalized email, a JSON field, a date bucket or another deterministic expression.