Indexes, Query Plans and Performance

1. Indexes as Access Structures

An index is an auxiliary structure that helps the DBMS locate rows without examining every table record. It stores indexed key values with information that leads to corresponding rows.

CREATE INDEX idx_employee_department
    ON employee(department_id);

The index can support queries that search, join, group or order by department_id. It does not change relational meaning and does not guarantee output order without ORDER BY.

An index trades faster access for storage, maintenance during writes, memory use and administrative work.

2. B-Tree-Style Indexes

MySQL commonly uses B-tree-style indexes. Their balanced ordered structure supports:

equality lookup;
ordered range lookup;
prefix traversal of composite keys;
minimum and maximum access;
ordered scans that may satisfy ORDER BY;
grouping in compatible key order.

A range such as salary BETWEEN 50000 AND 70000 maps naturally to an interval of ordered keys. Hash-like structures suit equality but do not naturally support ordered ranges.

3. Clustered InnoDB Storage

InnoDB normally organizes table records by primary key. The clustered index's leaf records contain the row data.

Secondary index entries contain their secondary key plus the primary-key value used to locate the clustered record. Therefore, a wide primary key enlarges every secondary index.

Choose a primary key that is stable, unique, non-NULL and reasonably compact. Randomly distributed keys can increase page splitting and reduce locality, while monotonically increasing identifiers can concentrate inserts. Workload and distributed-generation needs determine the tradeoff.

4. Primary, Unique and Ordinary Indexes

A primary-key index enforces primary identity. A unique index enforces uniqueness of its key values under MySQL's NULL rules. An ordinary index supplies an access path without a uniqueness rule.

Constraint and performance roles overlap but are not identical. Do not remove a required unique constraint merely because no current query appears to use its index. It protects data integrity.

Foreign-key columns often benefit from indexes because parent checks, child lookups, joins and referential actions use them. MySQL also imposes index requirements for enforced foreign keys.

5. Composite Indexes

A composite index contains several columns in a defined order:

CREATE INDEX idx_employee_dept_salary
    ON employee(department_id, salary);

It naturally orders first by department_id and then by salary within each department. It can support:

WHERE department_id = 10

and:

WHERE department_id = 10
  AND salary >= 50000

It usually cannot provide the same direct ordered search for salary alone because salary is not the leading column.

6. The Leftmost-Prefix Principle

For index (a, b, c), useful ordered prefixes are:

(a)
(a, b)
(a, b, c)

A query using a and c but not b may use a effectively but cannot usually use c as the next continuous ordered component.

Equality on earlier columns followed by a range on the next column is a strong pattern. After a range component, later columns may still help filtering or covering but often do not extend the same search range.

Column order should reflect important predicates, joins, ordering, selectivity and reuse across real queries.

7. Selectivity

Selectivity describes how strongly a predicate narrows candidates. A unique identifier is highly selective; a Boolean status may match half the table.

An index on a low-cardinality column can still help when one value is rare, when combined with other columns or when it covers the query. It may be useless when the predicate returns most rows.

The optimizer estimates selectivity from statistics and data distribution. “This column has an index” does not imply that using it is cheapest.

8. Covering Indexes

An index covers a query when it contains every column needed for filtering and output:

CREATE INDEX idx_order_customer_date_total
    ON order_header(customer_id, ordered_at, total_amount);

For a compatible query, InnoDB may answer from secondary index entries without fetching full clustered rows. EXPLAIN may report an index-only access indicator such as “Using index.”

Coverage is query-specific. Adding every output column makes indexes wide and expensive to maintain, so covering design should target important measured workloads.

9. Prefix Indexes

MySQL can index a prefix of a long string:

CREATE INDEX idx_customer_email_prefix
    ON customer(email(20));

This reduces index size but can decrease selectivity and cannot enforce full-value uniqueness unless the indexed prefix itself is guaranteed unique.

Choose prefix length from data-distribution measurements, not guesswork. Modern limits depend on engine, row format, character set and version.

10. Functional and Generated-Column Indexing

A predicate that applies a function may not use an ordinary index on the original column:

WHERE LOWER(email) = 'a@example.com'

Supported MySQL versions can index expressions or generated columns. Another solution is a collation whose comparison rules already match the requirement.

For dates, a range is often clearer:

WHERE ordered_at >= '2026-01-01'
  AND ordered_at <  '2027-01-01'

rather than YEAR(ordered_at) = 2026.

11. Sargable Predicates

A sargable predicate can be translated into a direct index search argument. Equality, bounded ranges and prefix LIKE patterns often qualify.

Common obstacles include:

wrapping the indexed column in a function;
implicit conversion between incompatible types;
arithmetic on the indexed column;
a leading wildcard such as LIKE '%text';
conditions not aligned with a composite prefix.

Rewrite without changing semantics. A computed indexed column may be appropriate when the transformed value is a frequent search key.

12. Why a Table Scan Can Be Correct

An index lookup involves traversing the index and possibly performing many primary-row lookups. When a query returns a large percentage of the table, sequentially scanning pages can be cheaper.

Small tables also make scans inexpensive. A scan is not automatically a performance defect.

Judge the plan from rows examined, I/O pattern, latency, concurrency and workload frequency. Forcing an index without evidence can make a query slower and more fragile as data distribution changes.

13. The Query Optimizer

The optimizer considers access methods, indexes, join orders, join algorithms, predicate placement, sorting, grouping and materialization. It estimates costs using metadata and statistics.

Cost estimates can be wrong when statistics are stale, data is skewed, predicates are correlated or parameter values vary widely.

The optimizer does not understand unstated business assumptions. Correct keys and constraints can improve both integrity and available optimization information.

14. EXPLAIN

EXPLAIN shows the chosen plan:

EXPLAIN
SELECT employee_id, salary
FROM employee
WHERE department_id = 10
  AND salary >= 50000;

Important fields can include access type, possible keys, selected key, key length, estimated rows, join order and extra operations.

Read the plan as a whole. Seeing an index name does not prove efficiency; it might scan the entire index, examine many rows, sort a large intermediate result or perform costly lookups.

15. EXPLAIN ANALYZE

Where supported, EXPLAIN ANALYZE executes the query and reports actual timing and row counts alongside estimates.

Large differences between estimated and actual rows reveal poor statistics, skew or model limitations. Because the query executes, use care with cost and with statements that can modify data.

Run tests with representative parameter values and data volume. A plan fast for one selective customer may be poor for another customer owning millions of rows.

16. Join Indexes

For a join:

FROM order_header AS o
JOIN order_line AS l
  ON l.order_id = o.order_id

an index on order_line(order_id) supports finding lines for each order. The order_header primary key already supports the opposite lookup.

Composite indexes can include additional join filters:

order_line(order_id, product_id)

Choose direction from likely driving tables and predicates. Indexing every join column separately may be inferior to a composite index matching the full access pattern.

17. Sorting and Grouping

An index can supply rows in an order compatible with ORDER BY and avoid a separate sort:

WHERE customer_id = ?
ORDER BY ordered_at DESC, order_id DESC

A matching index might begin with customer_id, ordered_at, order_id in compatible directions and version-supported form.

If filtering and ordering requirements conflict, the optimizer chooses a tradeoff. LIMIT makes ordered index access especially valuable when it can stop early.

Only ORDER BY defines the result-order contract, even if the selected plan happens to scan an index.

18. Index Write Costs

Each INSERT must add entries to every relevant index. UPDATE must change indexes containing modified keys. DELETE must remove entries. More and wider indexes increase:

write I/O and CPU;
transaction duration;
buffer-pool use;
lock and latch pressure;
storage and backup size;
schema-change time.

Duplicate or unused indexes should be identified through evidence and removed carefully after checking constraint roles and workload history.

19. Pagination

Offset pagination:

ORDER BY ordered_at DESC, order_id DESC
LIMIT 20 OFFSET 100000

may scan and discard many prior rows.

Keyset pagination continues after the last seen key:

WHERE (ordered_at, order_id) < (?, ?)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20

With a matching index, cost stays closer to page size. It also avoids shifts caused by newly inserted earlier rows. The ordering must include a unique tie-breaker.

20. Statistics

Statistics estimate table sizes, value distributions and selectivity. MySQL can refresh statistics through analysis operations and newer versions support histograms for selected columns.

Maintenance should respond to evidence of stale or inaccurate estimates. Refreshing statistics can change plans, so important queries should be observed afterward.

Statistics describe individual distributions imperfectly and may not capture correlation between columns. Composite indexes and schema constraints can provide more useful structure.

21. Query Shape Before Indexes

Indexes cannot repair an incorrect query. First verify join conditions, result grain, NULL behavior and predicates.

Reduce unnecessary columns and rows, pre-aggregate many-side data when appropriate, avoid repeated correlated work and use EXISTS for existence. Then index the resulting stable access patterns.

Adding indexes to compensate for SELECT *, accidental cross joins or non-sargable conversions creates maintenance cost without addressing the root problem.

22. Measuring Performance

Measure representative workloads with realistic data volume, distribution, concurrency and parameter values. Record latency percentiles, rows examined, I/O, lock waits, CPU and execution frequency.

A query executed once per day can tolerate a plan that would be unacceptable thousands of times per second. A microbenchmark on an empty development table does not predict production behavior.

Change one design assumption at a time, re-check plans and include write cost in the result.

23. An Index Design Method

For an important query:

state result correctness and row grain;
identify equality predicates and join keys;
identify range predicates;
identify ordering and grouping;
decide whether coverage is worthwhile;
propose the narrowest reusable composite index;
examine EXPLAIN and actual execution;
test write impact and other queries.

Index design is workload design. The best index is not the one with the most columns; it is the smallest maintainable structure that measurably supports important correct queries.