MariaDB Tutorial / Read EXPLAIN and Compare Plans Before and After an Index

EXPLAIN displays the optimizer’s selected execution plan. Compare the same query and returned results before and after indexing, and read access methods, chosen keys and estimated row counts without confusing estimates with measured runtime.

Read plan fields in context

Start with which tables are accessed and which keys can narrow their input. An ALL access type is not automatically wrong, and Using filesort does not alone prove a disk-file sort. Consider data size, selection ratio and actual latency. Use Design Single and Composite Indexes for Real Queries to justify candidate keys and the logging conditions in Inspect Server Settings, SQL Modes, Time Zones, and Logs to locate queries that are actually slow.

Check the MariaDB documentation for the syntax and conditions used here: explain.

Generate a reproducible thousand-row fixture

Connect to shopcourse and check that digits_40_lab and explain_40_lab do not exist. The complete setup combines three decimal digits to generate 1000 rows without reading or changing actual application orders. Customer IDs range from one to one hundred, with ten orders per customer.

USE shopcourse;
CREATE TABLE digits_40_lab(n TINYINT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO digits_40_lab VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE explain_40_lab(
 order_id INT PRIMARY KEY,customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO explain_40_lab
SELECT 1+a.n+10*b.n+100*d.n,
 MOD(a.n+10*b.n+100*d.n,100)+1,
 DATE_ADD('2026-01-01',INTERVAL (a.n+10*b.n+100*d.n) MINUTE),1000
FROM digits_40_lab a CROSS JOIN digits_40_lab b CROSS JOIN digits_40_lab d;

Verify SELECT COUNT(*) FROM explain_40_lab; returns 1000. This artificial distribution teaches plan interpretation; it does not represent production skew, table size or load.

Compare the identical EXPLAIN before and after indexing

EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

CREATE INDEX idx_e40_customer_date
ON explain_40_lab(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

The first plan has no secondary customer-time key; the second has a new candidate. Inspect the actual key choice and estimated rows in your output. Version, optimizer statistics and data distribution can change detailed estimates, so do not memorize one plan’s numbers as a universal result.

One observed plan from executing this thousand-row fixture on MariaDB 12.3.3 is shown below. Your values need not match: data, statistics and version can change the selected plan.

In this observation, a full scan became customer/time range access and estimated rows fell from 1000 to 10. This is not a measured hundredfold speedup. Verify the same ten returned orders and inspect execution data separately.

Verify actual output independently of estimated rows

SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

The returned identifiers are ten values beginning at 47 and increasing by 100 through 947. The same SELECT should return identical rows in the same order with or without the new index. EXPLAIN rows estimates an access step’s work, not the final result count.

A plan does not guarantee a latency. A meaningful timing comparison controls the query, data, concurrent load and cache conditions and uses repeated observations. Do not publish a service-wide improvement percentage from a tiny synthetic timing difference.

Separate executing ANALYZE from statistics maintenance

MariaDB’s ANALYZE statement family executes a query to obtain execution information. Do not treat it as a harmless EXPLAIN equivalent in front of UPDATE or DELETE. If runtime analysis is needed, use a suitable separate environment and inspect the precise scope and privileges first.

ANALYZE TABLE is a different operation that updates table statistics. Stale statistics can distort estimates, but refreshing statistics also has version-dependent costs and locking considerations. Investigate query structure and distribution before forcing a preferred plan with FORCE INDEX.

For actual execution information, apply the following only to this learning SELECT. ANALYZE FORMAT=JSON executes the query; distinguish measured fields such as r_rows and r_loops from estimated rows. Runtime depends on the environment and is not a fixed expected answer.

ANALYZE FORMAT=JSON
SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

ANALYZE FORMAT=JSON execution fields

Keep reproducible evidence and remove only this fixture

Record the server version, definitions and indexes, data-generation method, comparison query and before/after plans. After checking retention needs, remove only the two practice objects below. They have no foreign-key relationship, and no existing application-order table belongs in this cleanup.

DROP TABLE shopcourse.explain_40_lab;
DROP TABLE shopcourse.digits_40_lab;