Intermediate · 40 minutes · Guided lesson

Indexes and query plans

Understand why indexes can improve reads and add write/storage costs.

By the end of this lesson

  • Explain indexes and query plans and when it is useful
  • Perform a safe inspection or structured practice task
  • Verify the result and describe remaining uncertainty

Your environment: Use an unprivileged account and a disposable Linux lab. Commands and available utilities vary by distribution and version. Where a command is unavailable or would require production access, complete the scenario as an offline written exercise. Setup help

Understand the concept

An index provides a structure that can help a database find rows without scanning every row. Indexes consume storage and add maintenance work to writes. The optimiser chooses a plan based on query shape, statistics and engine behaviour. An index that seems relevant is not guaranteed to be selected or to improve the workload.

Use EXPLAIN or the engine’s plan inspection tool on a representative, safe query. Compare estimated and observed behaviour where available. Avoid running expensive profiling or changing indexes on production without a change plan, baseline and rollback.

Apply a careful workflow

Query-plan syntax and interpretation differ across engines. Confirm scope before consequential changes, keep a short record of expected and observed results, and consult local documentation when behaviour differs. Protect credentials and personal data in notes and logs.

Check your understanding

The goal of this lesson is not to memorise a single command. Explain why the proposed check is relevant, what its output can establish and what remains unknown. A successful command status is only one piece of evidence.

See it in context

Worked example

A bounded example to inspect in your own lab; adapt paths and options to your installed tools.

EXPLAIN SELECT id, created_at FROM sample_events WHERE created_at >= DATE "2026-01-01";

Read each command and identify its target, inputs, expected evidence and possible failure modes. Do not run administrative or modifying commands on a production system.

Your turn

Practical lab

Compare two fictional query plans and explain which evidence would support an index change and how you would measure its impact.

Run commands in your own lab. This website displays instructions; the practice terminal below helps you rehearse the syntax.

1. Prepare your workspace

mkdir -p "$HOME/training4linux/extended-labs" && cd "$HOME/training4linux/extended-labs"
printf "%s\n" "Practice workspace for Indexes and query plans" > lab-notes.txt

2. Complete the task

  1. Complete the scenario using the supplied synthetic information or a disposable local lab. Record what you observed and what you inferred.
  2. Compare your work against the success criteria and explain any differences.
  3. Write one verification step, one stop condition and a safe cleanup or recovery step.

3. Check your result

  • The recommendation relies on workload and plan evidence and includes write overhead and verification.
  • The work remains within the disposable workspace or an offline case study.
  • The report identifies at least one limitation or remaining uncertainty.
Need a hint?

Start with a read-only check, define the scope and choose the smallest next step that distinguishes competing explanations.

Reveal a worked solution

Compare this with your approach after trying the task. More than one valid solution may exist.

Use the success criteria as a review rubric. Record the reasoning and evidence behind your result rather than treating one exact command sequence as the only valid answer.

After the lab: Retain your notes if useful. Remove only files created in the disposable practice directory; no production system should be changed.

Common mistakes to avoid

  • Do not add indexes solely because a column appears in a WHERE clause.
  • Do not treat a successful command exit as proof that the overall service or business outcome is correct.

Check your understanding

Knowledge check

Answer all 3 questions correctly to pass. Retry as often as you need; your best score is kept.

1. Which approach best fits indexes and query plans?
2. What should you do before a consequential change?
3. What does a successful command exit status establish?

Your lesson progress

Not started · Best score: 0% · Attempts: 0

Practical completion is self-reported. Progress is linked to a private cookie in this browser and stored on the site. Clearing that cookie loses access to it. Manage or erase progress.

Keep the reference close

Further reading