MySQL composite indexes and honest EXPLAIN analysis

What you will learn

• Match a composite index to equality filters and ordered pagination.
• Inspect EXPLAIN without treating estimates as measured latency.
• Verify identical query results before/after an index change.

Theory: an index serves an access pattern

An index is a maintained search structure. It can reduce work for selected queries but adds storage and write-maintenance cost. Start with an actual predicate, order and result size; do not index every column without a reason.

This query filters by owner and status, orders by unique id, and takes five rows. A composite index in owner_id, status, id order matches the leading equality conditions and the remaining order. A different query using only status is not the same leftmost-prefix lookup. Avoid promising that every optimizer always chooses one particular index.

EXPLAIN describes a plan and estimates; it is not a benchmark proving elapsed-time improvement. Statistics, cardinality, data distribution, server version and table size can change plan choices. EXPLAIN ANALYZE actually executes supported statements; use it deliberately on safe representative work.

Worked example: a reproducible owner report

Required: MySQL 8.4/InnoDB and an isolated practice database. Save as sql_query_plans.sql. The recursive fixture has 512 dummy rows, below the usual 1000 recursive-iteration limit. Never change production recursion settings for this example. Laragon/XAMPP are optional; verify the actual engine.

CREATE TABLE study_plan_tasks (
    id INT PRIMARY KEY,
    owner_id INT NOT NULL,
    status VARCHAR(10) NOT NULL,
    title VARCHAR(40) NOT NULL
) ENGINE=InnoDB;
INSERT INTO study_plan_tasks (id, owner_id, status, title)
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 512
)
SELECT n, MOD(n, 32) + 1,
       CASE WHEN MOD(n, 3) = 0 THEN 'done' ELSE 'todo' END,
       CONCAT('Task ', n)
FROM numbers;
EXPLAIN SELECT id, title FROM study_plan_tasks
WHERE owner_id = 7 AND status = 'done' ORDER BY id LIMIT 5;
SELECT id, title FROM study_plan_tasks
WHERE owner_id = 7 AND status = 'done' ORDER BY id LIMIT 5;
CREATE INDEX study_owner_status_id ON study_plan_tasks (owner_id, status, id);
ANALYZE TABLE study_plan_tasks;
EXPLAIN SELECT id, title FROM study_plan_tasks
WHERE owner_id = 7 AND status = 'done' ORDER BY id LIMIT 5;
SELECT id, title FROM study_plan_tasks
WHERE owner_id = 7 AND status = 'done' ORDER BY id LIMIT 5;

Run and compare results

Windows PowerShell, from the practice file's directory:

mysql --user=practice_user --password practice_learning

Ubuntu Bash, from the practice file's directory:

mysql --user=practice_user --password practice_learning

Inside the MySQL client:

SELECT DATABASE();
SOURCE sql_query_plans.sql;

Confirm SELECT DATABASE() before SOURCE; substitute your isolated account/database and enter the password at the prompt. SOURCE belongs to the MySQL client. If the table exists already, stop and inspect it instead of dropping data.

Both SELECT results must contain IDs 6, 102, 198, 294, 390 in that order, with titles Task 6, Task 102, Task 198, Task 294, Task 390. The table has 512 rows. Plan display/estimates can vary; record your own before/after plan. The test checks the new index is a possible key and has exactly the declared ordered columns, not a universal chosen-plan or speed claim. Windows MySQL8.4.3 executed; Ubuntu not executed.

Guided lab

1. Predict the matching IDs from the dummy-data rule. Compare exact ordered rows before/after indexing.
2. Inspect possible_keys, key, type, rows and Extra in your EXPLAIN output. Distinguish candidate keys from the key actually chosen.
3. Inspect SHOW INDEX column order and non-unique status. This performance index is not a uniqueness rule.
4. Change the query to status-only in a disposable copy and compare plans. Do not assume a leftmost prefix still matches.
5. Explain why a tiny fixture is useful for correctness but insufficient to promise production latency. For performance work, define representative data, parameters and repeatable measurements.

Independent exercise

Add keyset pagination for the next page of the same report: filter id greater than the previous page’s last id while keeping owner/status and order. Explain why an id cursor from another owner is not permission to read their rows.

Test no overlap, stable ascending order, a final short page and empty results. Compare with OFFSET behavior conceptually; do not invent timing numbers. Hint: preserve both ownership filters and use the unique ordered id as the cursor. Define how new or changed rows affect your pagination contract.

Common mistakes and interview

Avoid confusing an index with authorization, adding redundant indexes without measuring writes/storage, dropping ORDER BY while claiming stable pages, treating possible_keys as the chosen key, and presenting estimated rows as measured milliseconds.

Interview: “How would you investigate a slow owner-scoped report?” Start with correctness/ownership and actual query, inspect plans/statistics and access patterns, propose a measured change, then verify the same ordered results and representative workload cost.

English practice and glossary

Composite index: an index over ordered columns. Leftmost prefix: leading indexed columns usable for an access pattern. Predicate: a filtering condition. Cardinality: distinct-value information relevant to estimates. Plan: an execution strategy. Keyset cursor: a boundary based on ordered values.

Write: “The query filters __ and orders __. This index may help because __. I verified __. I have not measured __.” Save actual plans with dummy data in the journal.

Recap and next step

Choose indexes for concrete queries and inspect real plans. Preserve ownership and ordered results; measure performance separately. Next apply transactions, query boundaries and eager-loading tests inside Laravel services and APIs.