Introduction
An index can make a query faster, but understanding the improvement requires looking at the work performed. The database may examine fewer records, avoid sorting, or retrieve data through a different access path.
This lab connects those changes to storage and execution plans. We will inspect a recent-orders query, add a candidate index, and compare the resulting plan without assuming the optimizer must use it.
Define the Experiment
The application needs the latest five orders for one customer.
We will compare two situations:
A table with a primary key but no customer-and-date index.
The same table after adding an index on
(customer_id, created_at).
Keep the query and data unchanged between comparisons. This helps isolate the effect of the new access path.
Use a practice database for the exercise. EXPLAIN output and identity-generation syntax vary across database systems; the examples use explicitly supplied IDs and LIMIT.
Create the Practice Table
CREATE TABLE orders_lab ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, created_at TIMESTAMP NOT NULL, total DECIMAL(12, 2) NOT NULL);Insert a small dataset:
INSERT INTO orders_lab (order_id, customer_id, created_at, total)VALUES (501, 101, '2026-10-01 09:00:00', 100.00), (502, 102, '2026-10-02 10:00:00', 80.00), (503, 101, '2026-10-03 11:00:00', 120.00), (504, 101, '2026-10-04 12:00:00', 60.00), (505, 103, '2026-10-05 13:00:00', 90.00), (506, 101, '2026-10-06 14:00:00', 75.00), (507, 102, '2026-10-07 15:00:00', 110.00), (508, 101, '2026-10-08 16:00:00', 40.00), (509, 101, '2026-10-09 17:00:00', 95.00), (510, 103, '2026-10-10 18:00:00', 130.00);These records make the result easy to verify. They are too few to demonstrate realistic performance differences.
For meaningful measurements, repeat the experiment with a larger dataset containing many customers and a representative distribution of orders.
Inspect the Baseline Plan
Run:
EXPLAINSELECT order_id, created_at, totalFROM orders_labWHERE customer_id = 101ORDER BY created_at DESCLIMIT 5;Without a suitable customer-and-date index, a possible strategy is to scan records, retain customer 101’s orders, and perform sorting or top-N processing.
Record what the plan actually shows:
Observation | Question |
|---|---|
Access method | Is it scanning the table or using an index? |
Estimated records | How much input does it expect to examine? |
Ordering | Is there a separate sort? |
Output limit | Where is the five-row restriction applied? |
Operator names and reported measurements differ by database. Do not expect one universal plan format.
Add a Composite Index
CREATE INDEX idx_orders_lab_customer_createdON orders_lab (customer_id, created_at);Run the same EXPLAIN statement again.
The index groups entries by customer, then by creation time. A compatible access method may locate customer 101’s entries, traverse them newest first, and stop after finding five qualifying records.
Additional row access may still be required because the query also requests order_id and total. Whether these values are available from the index depends on its contents and the storage design.
The index creates an opportunity; it does not force a particular plan. On this tiny table, the optimizer may still prefer a scan.
Before and after indexing
Verify That the Result Is Unchanged
Execute the query without EXPLAIN:
SELECT order_id, created_at, totalFROM orders_labWHERE customer_id = 101ORDER BY created_at DESCLIMIT 5;The expected order IDs are:
Position | order_id |
|---|---|
1 | 509 |
2 | 508 |
3 | 506 |
4 | 504 |
5 | 503 |
The sample timestamps are distinct. If real records can share a timestamp and deterministic ordering matters, add a suitable tie-breaker to both the query and candidate index.
An optimization must preserve the required answer.
Connect the Plan to Storage
A table scan examines records through table pages. An index access path reads index pages and may then fetch additional pages containing the required rows.
The number of physical reads depends partly on buffering:
Already buffered pages can be reused.
Missing pages must be loaded, possibly through another cache layer.
Repeated row lookups can revisit the same page.
One returned row does not necessarily correspond to one page read.
A smaller result therefore does not prove that little storage work occurred. Inspect available buffer or I/O measurements when the database provides them.
Same result, different work
Measure Without Misreading the Results
Where supported, collect actual execution statistics as well as estimates. Commands that report actual operator measurements may execute the query, so check their behavior before using them.
Compare:
Estimated and actual record counts.
Records examined or processed.
Sorting and temporary-storage activity.
Buffer accesses and physical reads, where reported.
Execution time across several comparable runs.
A second run may benefit from pages cached by the first. Avoid attributing that entire improvement to the index.
Do not clear shared production caches for this experiment. Use a controlled practice environment and describe whether measurements represent warm or cold conditions.
Summary
This lab compares a query before and after adding a composite index. The useful observations are changes in access paths, ordering work, record processing, and page access, not simply whether an index appears in the plan.
Verify the result first, then measure with representative data and comparable conditions. Small examples explain the mechanics, while realistic workloads reveal whether the design provides a practical benefit.
Be the first to add a comment.