MariaDB / Intermediate: concurrency and reusable SQL / Lesson 8

Indexes and reading EXPLAIN

Choose an index for a concrete shipment query and read a plan without inventing benchmark results. Balance lookup benefits against write and storage costs.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Relate composite index order to predicates and ordering.
Distinguish estimated plans from executed analysis.

An index is an alternate access path

A B-tree index orders keys so the engine can locate a useful subset without scanning every row. It is not a universal speed switch. A small table or a query matching most rows may be cheaper to scan. InnoDB clusters table data around the primary key, and secondary indexes carry primary-key information, so a wide primary key also enlarges secondary structures. Every additional index consumes space and increases work for inserts, updates and deletes.

Match a shipment lookup

For a customer-specific recent shipment query, place customer_id first because it is constrained by equality, then created_at for the range. shipment_id provides a stable tie-breaker. This is a candidate design, not a guarantee of a particular plan. Table size, distribution, selected columns and existing indexes matter. The DDL below belongs only in a reviewed disposable lab fixture. EXPLAIN estimates access before execution; the actual rows and cost cannot be inferred from these column names alone.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE INDEX ix_ship_customer_time
ON shipments (customer_id, created_at, shipment_id);
EXPLAIN SELECT shipment_id, created_at FROM shipments
WHERE customer_id = 7 AND created_at >= '2026-10-01'
ORDER BY created_at, shipment_id LIMIT 20;

Look for the chosen key and estimated rows. Exact plan cells depend on a populated fixture and server version.

Interpret several plan signals together

In traditional EXPLAIN output, possible_keys names candidates and key is the chosen index. The type column describes the access method: ALL is a table scan, while ref and range indicate other forms of indexed access. rows is an estimate, not a measured count. Using filesort describes a sorting operation and does not by itself prove disk I/O. Using index can mean covering access, not that a selective lookup occurred. Judge the complete query and workload.

Keep the indexed column searchable

A half-open timestamp range states boundaries directly and is often a good baseline. Wrapping the column in DATE() has historically prevented straightforward range access; newer MariaDB versions can optimize some date predicates, so verify the deployed plan instead of teaching a permanent prohibition. The two forms below express one day under the same time convention. Compare their estimated plans in a representative lab and confirm identical logical results before choosing a rewrite.

sql Illustrative SQL; expected results are reasoned, not measured.

EXPLAIN SELECT shipment_id FROM shipments
WHERE DATE(created_at) = '2026-10-01';
EXPLAIN SELECT shipment_id FROM shipments
WHERE created_at >= '2026-10-01'
  AND created_at < '2026-10-02';

No speed ratio is claimed. The second form exposes an explicit range; optimizer transformations are version-dependent.

Execution analysis really executes

MariaDB ANALYZE SELECT and ANALYZE FORMAT=JSON SELECT execute the query and add observed execution information such as actual row statistics. They differ from ANALYZE TABLE, which updates optimizer statistics. Do not paste MySQL EXPLAIN ANALYZE syntax assuming identical support or output. Begin with EXPLAIN and a representative read-only lab query. Evaluate latency distributions, rows examined and write overhead after an index change; a faster single cached run does not establish production benefit.

Common errors and fixes

An index exists but the query still scans

Why it happens: The predicate may be unselective, use the wrong leading columns or require conversion.
How to fix it: Inspect the plan, data distribution and parameter types before adding another index.
Prevent it next time: Design indexes from measured query patterns and realistic data.

rows is reported as a benchmark count

Why it happens: Estimated EXPLAIN output was mistaken for executed instrumentation.
How to fix it: Label estimates and obtain controlled ANALYZE observations separately.
Prevent it next time: Record which statement produced each diagnostic.

Writes slow after adding many indexes

Why it happens: Every mutation maintains the extra index structures.
How to fix it: Review redundant indexes against the full workload before controlled removal.
Prevent it next time: Include write throughput and storage in index acceptance criteria.

Check your understanding

1. Can an index on (customer_id, created_at) efficiently start a lookup using only created_at in every case?

Show worked solution

No general guarantee follows. Its ordering groups by customer first, so the ordinary leftmost-prefix rule favors customer equality or a customer-led range. The optimizer may choose scans or version-specific alternatives. Inspect the actual query plan and consider another index only when workload evidence justifies its maintenance cost.

2. A plan says Using filesort for a twenty-row result. Should you immediately eliminate it?