An index can help a database find rows without scanning all of them, but it also has storage and write costs. Choose one to support a real query and verify its effect with a query plan and representative data.
Identify the query pattern
Record the filters, joins and ordering that matter to a slow query. An index on one column does not automatically accelerate every expression involving that column. Composite index usefulness depends on the database and the query pattern. Begin with an observed workload instead of indexing every field in anticipation.
Read a plan as evidence, not a guarantee
A query plan describes the strategy chosen under current conditions. Small tables, selectivity, statistics and available indexes influence the choice. The optimiser may reasonably prefer a scan. Compare the plan and measured behaviour before and after a change, using representative data rather than assuming the presence of an index proves improvement.
Account for the write side
Indexes must be maintained when indexed values change. Extra indexes consume space and can increase the cost of inserts, updates and deletes. Remove a demonstrated redundancy only after checking the other queries that may depend on it. Keep schema changes reviewable and plan a safe rollout for a busy database.
Worked example
The example creates a small index and requests a SQLite query plan for an equality filter. The exact plan output can vary by engine and version; the exercise is to inspect the chosen strategy, not memorise a particular line of text.
CREATE TABLE tickets (id INTEGER PRIMARY KEY, owner_id INTEGER, title TEXT);
INSERT INTO tickets VALUES (1,7,'First'), (2,9,'Second'), (3,7,'Third');
CREATE INDEX tickets_owner ON tickets(owner_id);
EXPLAIN QUERY PLAN SELECT id, title FROM tickets WHERE owner_id = 7;
Check your understanding
Try each question before opening its explanation. These are free practice prompts; they do not use game chances.
Must the optimiser use every available index?
No. A scan may be cheaper for a small table or a query returning much of the data. The plan is a decision based on the available information.
Why not index every column?
Each index has storage and maintenance costs. Add indexes to support justified access patterns, then evaluate the whole workload rather than one isolated read.
What should you compare after adding an index?
Compare the relevant query plan, measured latency or work, result correctness and write costs using data representative of the actual workload.
Sources and further reading
These original examples explain foundational concepts. Use the official references for complete, current details and version-specific behaviour.
About this guide: AI-assisted educational content with original examples and linked primary references. Found an issue? Tell us what to improve.