Chapter 08 · Index Engineering and Access Path Design
Designing an Index Portfolio from Workload Evidence and Write-Cost Constraints
Turn individual index tricks into an operational portfolio: collect representative query shapes, rank demonstrated benefit, test removal safely, measure write/storage cost, and document keep/drop decisions.
Learning outcomes
A mature system does not have “the index for this query.” It has an index portfolio: a small set of correctness and performance structures that must serve many reads while every insert, delete, and indexed-column update maintains them. The goal is not zero redundant-looking indexes or the highest possible hit rate. The goal is a documented set whose benefit is larger than its total operational cost.
Collect representative query shapes and index-use evidence without treating a short observation window as complete workload history.
Rank candidate indexes by demonstrated read benefit, correctness requirements, and workload frequency.
Use sys schema, Performance Schema, EXPLAIN, and invisible indexes to review overlap and removal risk.
Measure local write/storage cost with controlled twin-table experiments rather than quoting universal index overhead percentages.
Produce a keep/drop/merge decision record that states assumptions, rollback path, and post-change monitoring signals.
Start with workload evidence, not DDL
Before changing indexes, inventory the important workload. In a production environment, application traces, slow-query logs, Performance Schema digests, APM, and business criticality all contribute. On this local lab, execute representative shapes several times so Performance Schema/sys summaries have something to observe.
USE servicehub_index_lab;SELECT work_order_id,opened_at FROM work_orders WHERE tenant_id=9 AND status='open' ORDER BY opened_at DESC LIMIT 30;SELECT work_order_id,status,opened_at FROM work_orders WHERE technician_id=150 AND opened_at>='2026-03-01' ORDER BY opened_at DESC LIMIT 30;SELECT work_order_id,status FROM work_orders WHERE tenant_id=9 AND customer_name='Customer 120';SELECT tenant_id,MIN(opened_at),MAX(opened_at) FROM work_orders GROUP BY tenant_id;SELECT DIGEST_TEXT,COUNT_STAR,SUM_TIMER_WAIT,SUM_ROWS_EXAMINED,SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub_index_lab'ORDER BY SUM_TIMER_WAIT DESCLIMIT 15;events_statements_summary_by_digest aggregates normalized statement shapes. It is valuable when Performance Schema consumers have been collecting a representative interval. A server restarted five minutes ago—or a test environment that never ran month-end reports—cannot prove an index is unused in production.
Inventory indexes and classify why each exists
SHOW INDEX FROM work_orders;SELECT table_schema,table_name,redundant_index_name,redundant_index_columns, dominant_index_name,dominant_index_columnsFROM sys.schema_redundant_indexesWHERE table_schema='servicehub_index_lab';SELECT object_schema,object_name,index_nameFROM sys.schema_unused_indexesWHERE object_schema='servicehub_index_lab'ORDER BY object_name,index_name;SELECT *FROM sys.schema_index_statisticsWHERE table_schema='servicehub_index_lab' AND table_name='work_orders'ORDER BY rows_selected DESC;Now classify every key:
| Class | Examples | Decision standard |
|---|---|---|
| Correctness | PRIMARY, UNIQUE, foreign-key-supporting index. | Keep unless the constraint/design itself changes and another key safely satisfies the requirement. |
| Critical read path | Tenant dashboard, technician history. | Keep when representative plans and frequency show meaningful benefit. |
| Specialized search | FULLTEXT, functional SLA, multi-valued JSON. | Keep only if the matching predicate is real and frequent enough to justify maintenance. |
| Experimental/overlap | Prefix tests, duplicate leading prefixes, one-off tuning keys. | Remove or consolidate after reversible plan testing and workload review. |
sys.schema_unused_indexes explicitly depends on observed events. Treat “unused” as “not observed during this instrumentation window,” not “safe to drop forever.”
Build a candidate decision table with plan evidence
EXPLAIN ANALYZESELECT work_order_id,opened_at FROM work_ordersWHERE tenant_id=9 AND status='open'ORDER BY opened_at DESC,work_order_id LIMIT 30;ALTER TABLE work_orders ALTER INDEX ix_wo_dashboard INVISIBLE;EXPLAIN ANALYZESELECT work_order_id,opened_at FROM work_ordersWHERE tenant_id=9 AND status='open'ORDER BY opened_at DESC,work_order_id LIMIT 30;ALTER TABLE work_orders ALTER INDEX ix_wo_dashboard VISIBLE;If hiding the index introduces a larger scan or sort, that is direct evidence of read-path value for this query. It still does not quantify workload frequency or write cost. Conversely, if the plan barely changes, investigate whether another index subsumes it before dropping anything.
MySQL continues maintaining an invisible index on writes. Use invisibility to test optimizer dependence and rollback quickly; use an actual DROP only after the decision is approved if you want storage and write-maintenance savings.
Measure write amplification with twin tables
Rather than timing the live work_orders table before and after destructive DDL, build two disposable tables with identical columns and different index portfolios. The numbers are local observations only.
DROP TABLE IF EXISTS write_light,write_heavy;CREATE TABLE write_light ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL,status VARCHAR(16) NOT NULL, opened_at DATETIME NOT NULL,customer_name VARCHAR(120) NOT NULL, payload VARCHAR(200) NOT NULL, KEY ix_light_tenant_status (tenant_id,status,opened_at)) ENGINE=InnoDB;CREATE TABLE write_heavy LIKE write_light;CREATE INDEX ix_heavy_status ON write_heavy(status);CREATE INDEX ix_heavy_opened ON write_heavy(opened_at);CREATE INDEX ix_heavy_customer ON write_heavy(customer_name);CREATE INDEX ix_heavy_tenant_customer ON write_heavy(tenant_id,customer_name);CREATE INDEX ix_heavy_cover ON write_heavy(tenant_id,status,opened_at,customer_name);SET SESSION cte_max_recursion_depth=12000;SET @t0=NOW(6);INSERT INTO write_light(tenant_id,status,opened_at,customer_name,payload)WITH RECURSIVE seq AS (SELECT 1 n UNION ALL SELECT n+1 FROM seq WHERE n<10000)SELECT 1+MOD(n,40),ELT(1+MOD(n,5),'open','assigned','waiting','closed','cancelled'), TIMESTAMP('2026-01-01')+INTERVAL MOD(n,180) DAY, CONCAT('Customer ',MOD(n,900)),REPEAT('x',80) FROM seq;SELECT TIMESTAMPDIFF(MICROSECOND,@t0,NOW(6)) AS light_insert_us;SET @t1=NOW(6);INSERT INTO write_heavy(tenant_id,status,opened_at,customer_name,payload)SELECT tenant_id,status,opened_at,customer_name,payload FROM write_light;SELECT TIMESTAMPDIFF(MICROSECOND,@t1,NOW(6)) AS heavy_insert_us;SELECT NAME,NUM_ROWS,CLUST_INDEX_SIZE,OTHER_INDEX_SIZEFROM information_schema.INNODB_TABLESTATSWHERE NAME IN ('servicehub_index_lab/write_light','servicehub_index_lab/write_heavy');Do not expect a fixed percentage difference. The heavy table maintains more B-trees and normally consumes more secondary-index pages, but exact elapsed time depends on cache, redo, storage, CPU, background work, and whether the second run benefits from warmed infrastructure. Repeat, alternate order, and report medians if you need a meaningful local benchmark.
Tempting but ineffective: “drop every unused index”
An index can be absent from the observed usage view because the server recently restarted, a quarterly job has not run, the query only appears during incidents, or instrumentation was disabled. Before a production drop:
| Step | Evidence / control |
|---|---|
| 1. Identify purpose | Schema migration history, constraint ownership, application/query owner. |
| 2. Confirm observation window | Uptime, Performance Schema collection, business cycles, deployments. |
| 3. Check overlap | SHOW INDEX, sys.schema_redundant_indexes, equality/order semantics. |
| 4. Test read-plan absence | Make the noncritical candidate invisible; exercise representative queries and alerts. |
| 5. Measure write/storage benefit | Disposable benchmark or canary metrics; invisible alone does not save write cost. |
| 6. Drop with rollback plan | Record DDL to recreate, online-DDL implications, maintenance window if needed. |
| 7. Monitor | Latency percentiles, rows examined, slow-query/digest regressions, write throughput, storage/cache metrics. |
Write the portfolio decision record
-- KEEP ix_wo_dashboard (tenant_id,status,opened_at DESC,work_order_id)-- Reason: high-frequency tenant queue; avoids larger scan/sort in representative plan.-- Evidence: EXPLAIN ANALYZE before/invisible test; digest frequency from representative window.-- Cost: maintained on every work_order write; secondary pages in buffer/storage.-- REVIEW ix_wo_customer_prefix(customer_name(8))-- Reason: lab experiment; exact tenant+customer query may need different composite key.-- Next test: compare (tenant_id,customer_name) against prefix key using production-shaped data.-- KEEP ix_wo_technician-- Reason: foreign-key support plus technician lookup path; confirm any replacement key before change.-- DROP CANDIDATE temporary/overlap indexes only after invisible test + full workload window.That small record is the difference between index engineering and archaeology. A future operator can see which query justified the index, what evidence was used, and which metric should trigger reconsideration.
Knowledge check
- Why is a short sys.schema_unused_indexes observation window insufficient for a drop decision?
- What does an invisible-index test tell you?
- Why use twin tables to compare write cost?
- What should a keep/drop record contain?
- What is the central Chapter 08 principle?
Reveal answers
- It only proves no indexed events were observed during that window; infrequent, seasonal, incident, or post-restart workloads may be missing.
- How normal optimizer plans behave when that index is unavailable, with fast reversibility. It does not remove index maintenance/storage cost.
- They let you compare different index portfolios without destructively altering the main lab table and make the extra maintenance work observable.
- Purpose/query owner, plan/workload evidence, correctness dependencies, measured costs, rollback/recreate path, and monitoring signals.
- Indexes are a workload portfolio: keep a small evidence-backed set whose read/correctness value justifies its write, memory, storage, and maintenance cost.
Chapter 08 summary and bridge to optimizer engineering
You can now reason from B-tree ordering instead of slogans, choose composite order from workload requirements, identify covering and prefix tradeoffs, use functional/multi-valued/FULLTEXT/SPATIAL families for their intended predicates, and interpret ICP/loose/tight/skip-scan access as optimizer choices rather than superstitions. Most importantly, you can defend an index portfolio with evidence and state its cost.
Chapter 09—Optimizer, EXPLAIN, Statistics, and Query Plan Engineering—builds directly on this portfolio. It asks why MySQL chooses one available path over another, how estimates and histograms affect cost, how EXPLAIN ANALYZE exposes misestimation, and how to handle plan regressions without turning hints into permanent debt.
DROP DATABASE IF EXISTS servicehub_index_lab;