Composite Index Ordering, Covering Indexes, Prefix Indexes, and Redundant Indexes

Learning outcomes

Once a useful composite index exists, a second danger appears: “just add another index” for every variation of the query. That strategy can produce a forest of overlapping B-trees that compete for memory, slow writes, and make maintenance harder. This lesson designs column order deliberately and then inventories overlap.

Column order begins with the query contract

Suppose ServiceHub’s tenant dashboard filters by tenant and status, asks for recent rows, and sorts newest first. We want one index that supports that repeated shape rather than three unrelated single-column indexes.

USE servicehub_index_lab;DROP INDEX ix_wo_tenant_status_opened ON work_orders;CREATE INDEX ix_wo_dashboard  ON work_orders (tenant_id, status, opened_at DESC, work_order_id);ANALYZE TABLE work_orders;EXPLAIN ANALYZESELECT work_order_id, opened_atFROM work_ordersWHERE tenant_id=9 AND status='open'ORDER BY opened_at DESC, work_order_idLIMIT 30;

Equality columns commonly make useful leading parts because they isolate a slice before the range/order portion. But “put the most selective column first” is not a complete design rule. If every important query begins with tenant_id for isolation and no query is allowed to search globally by status, leading with status merely because it has a different selectivity would ignore the workload contract.

Covering is query-specific, not an index label

An index is covering for a particular query when the engine can obtain all required columns from that index entry without fetching additional base-row columns. Because an InnoDB secondary index contains its own key parts plus the clustered primary key, selecting work_order_id does not necessarily require adding it as a separate trailing key part for coverage. We keep it explicit here mainly to stabilize tie ordering and make the intended key visible to learners.

EXPLAINSELECT work_order_id, opened_atFROM work_ordersWHERE tenant_id=9 AND status='open'ORDER BY opened_at DESC, work_order_idLIMIT 30;EXPLAINSELECT work_order_id, opened_at, customer_name, detailsFROM work_ordersWHERE tenant_id=9 AND status='open'ORDER BY opened_at DESC, work_order_idLIMIT 30;

The first plan may report Using index, indicating index-only access for the selected columns. The second needs customer_name and details, so it normally requires clustered-row reads. Adding a 255-character name plus TEXT content to every dashboard index would be an expensive response to a projection problem; first ask whether the UI actually needs those columns in the list view.

Prefix indexes deliberately throw information away

For long strings, MySQL can index only the leading characters. This can shrink index entries, but the prefix may collapse many distinct full values into the same key. Measure candidate prefix lengths before choosing one.

SELECT  COUNT(*) AS rows_total,  COUNT(DISTINCT customer_name) AS full_distinct,  COUNT(DISTINCT LEFT(customer_name,4)) AS d4,  COUNT(DISTINCT LEFT(customer_name,8)) AS d8,  COUNT(DISTINCT LEFT(customer_name,12)) AS d12FROM work_orders;CREATE INDEX ix_wo_customer_prefix ON work_orders(customer_name(8));SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_customer_prefix';

The prefix index can help rule out many nonmatching rows when the leading characters are selective enough. It does not store the full string key, so MySQL may need to inspect the base row to distinguish values that share the prefix. Prefix indexes also have limitations for operations that require the complete indexed value; for example, a prefix key does not qualify for MySQL’s Loose Index Scan grouping optimization that requires full column values.

EXPLAIN ANALYZESELECT work_order_id, customer_nameFROM work_ordersWHERE customer_name LIKE 'Customer 12%';EXPLAIN ANALYZESELECT work_order_id, customer_nameFROM work_ordersWHERE customer_name LIKE '%12%';

A leading wildcard generally prevents a normal B-tree range from starting at the beginning of the string. Making the prefix index longer does not repair a predicate whose search pattern has no known left edge.

Redundant indexes: overlap has a cost

Create deliberate overlap so the diagnosis is concrete:

CREATE INDEX ix_wo_tenant ON work_orders(tenant_id);CREATE INDEX ix_wo_tenant_status ON work_orders(tenant_id,status);CREATE INDEX ix_wo_dashboard_duplicate  ON work_orders(tenant_id,status,opened_at DESC,work_order_id);ANALYZE TABLE 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'  AND table_name='work_orders';

The sys.schema_redundant_indexes view identifies indexes duplicated or made redundant by another key. Treat its output as a review queue, not an automatic DROP script. Unique constraints, foreign-key support, prefix semantics, visibility, and workload-specific ordering still need human validation.

Invisible indexes make read-plan testing reversible

Rather than immediately dropping ix_wo_tenant_status, make it invisible and inspect representative plans:

ALTER TABLE work_orders ALTER INDEX ix_wo_tenant_status INVISIBLE;SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_tenant_status';EXPLAIN SELECT work_order_idFROM work_ordersWHERE tenant_id=14 AND status='assigned';EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */ work_order_idFROM work_ordersWHERE tenant_id=14 AND status='assigned';

Invisible indexes are still maintained by DML and still occupy storage. Visibility testing therefore answers “what happens to read plans if the optimizer cannot use this index?” It does not measure the write/storage savings of actually dropping it. Restore visibility before continuing unless you intentionally decide to remove it later.

ALTER TABLE work_orders ALTER INDEX ix_wo_tenant_status VISIBLE;DROP INDEX ix_wo_dashboard_duplicate ON work_orders;DROP INDEX ix_wo_tenant ON work_orders;DROP INDEX ix_wo_tenant_status ON work_orders;SHOW INDEX FROM work_orders;

Hands-on design exercise

For each query below, write the smallest index you think can serve the important predicate/order contract. Then compare it with the existing dashboard index. If you propose a new index, state which existing index becomes redundant and what write cost you are accepting.

-- A: tenant queue ordered by newestSELECT work_order_id,opened_at FROM work_ordersWHERE tenant_id=? AND status=? ORDER BY opened_at DESC,work_order_id LIMIT 50;-- B: one technician's recent historySELECT work_order_id,status,opened_at FROM work_ordersWHERE technician_id=? AND opened_at>=? ORDER BY opened_at DESC LIMIT 50;-- C: exact customer-name lookup inside a tenantSELECT work_order_id,status FROM work_ordersWHERE tenant_id=? AND customer_name=?;

Production judgment and bridge

Every additional index should have an owner and a workload reason. A covering index can be excellent for a high-frequency narrow query, but a giant covering index can evict useful pages and amplify every write. Prefixes can save space but sacrifice information. Invisible indexes can de-risk plan testing but not quantify drop-time write savings.

Lesson 3 moves beyond ordinary B-trees. Some predicates are about expressions, array membership, token relevance, or geometry. Those require specialized index families with different semantics and restrictions.

Authoritative references

• MySQL 8.4 Reference Manual — CREATE INDEX: Prefix and Functional Key Parts
• MySQL 8.4 Reference Manual — Invisible Indexes
• MySQL 8.4 Reference Manual — sys.schema_redundant_indexes
• MySQL 8.4 Reference Manual — Foreign Key Optimization
• MySQL 8.4 Reference Manual — SHOW INDEX