What is a database index and what is its main trade-off?
An index is an auxiliary data structure that maps column values to row locations, letting the database find matching rows quickly instead of doing a full table scan. The classic structure is the B-tree, which supports efficient equality and range lookups in logarithmic time. The trade-off: indexes consume storage and must be updated on every INSERT/UPDATE/DELETE, so they speed up reads but slow down writes. Over-indexing a write-heavy table hurts performance — you index the columns you actually query and filter on.
2 / 5
What is a composite index and why does column order matter?
A composite (multi-column) index indexes several columns together, e.g. (last_name, first_name). Column order is critical because of the leftmost-prefix rule: the index can serve queries filtering on last_name, or last_name AND first_name, but not a query filtering only on first_name. So you order columns by how they are queried — most selective and most commonly filtered first, with equality columns before range columns. Designing the column order to match your query patterns is the key skill in composite indexing.
3 / 5
What is a covering index?
A covering index includes every column a particular query requires — both the columns it filters on and the columns it returns. When this happens, the database performs an index-only scan: it answers entirely from the index without touching the table's row storage (the heap), avoiding extra I/O. For example, an index on (user_id, status) covers SELECT status WHERE user_id = ?. Many databases let you add non-key "included" columns to an index specifically to make it covering. It is a powerful optimization for hot read queries.
4 / 5
What does the query planner / EXPLAIN output tell you about index usage?
The query planner chooses an execution strategy, and EXPLAIN (or EXPLAIN ANALYZE) reveals it: which indexes are used, the join order, estimated vs actual rows, and whether a costly sequential/full table scan is happening where you expected an index scan. Reading EXPLAIN output is the primary tool for diagnosing slow queries: a "Seq Scan" on a large filtered table usually signals a missing index, while an unused index you created points to a leftmost-prefix or type-mismatch problem. Always verify with real data, since the planner relies on table statistics.
5 / 5
Why can adding too many indexes harm a database?
Indexes are not free. Every write must update all relevant indexes, so a table with many indexes pays a write-amplification cost on every INSERT/UPDATE/DELETE. They also consume disk and memory (indexes are cached too), and overlapping/redundant indexes waste resources while giving the planner more options to evaluate. The discipline is to index for your actual query patterns, remove unused indexes (the planner can show which are never chosen), and prefer a few well-designed composite/covering indexes over many single-column ones. It is a read-speed vs write-cost balance.
What does the "Database Indexing" vocabulary exercise cover?
This exercise tests real IT vocabulary related to database indexing through 5 multiple-choice questions, each built from realistic workplace sentences rather than abstract definitions.
Is this vocabulary exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is completely free — no account, sign-up, or payment required.
How many questions does this exercise have?
This exercise has 5 questions. Each one shows a real-world sentence or scenario with multiple-choice options and an explanation once you answer.
What happens after I answer a question?
You'll see immediate feedback showing whether your answer was correct, along with a short explanation of why — then a button to move to the next question, and a full results screen at the end.
Can I retry the exercise if I get questions wrong?
Yes. Once you reach the results screen, click "Try again" to reset your answers and go through the exercise from the start as many times as you like.
Do I need to create an account to take this exercise?
No account is needed. Your answers are scored in your browser during the session — nothing is saved to a server, so you can jump straight in.
Is my progress saved if I leave the page?
No — progress within an exercise resets if you navigate away or reload. Each exercise is short enough to complete in a few minutes in one sitting.
Are these vocabulary exercises connected to other topics?
Yes — this module shares real-world context with 9 other vocabulary modules. See "Related vocabulary" below to keep building a connected skill set.
How is this different from reading a glossary or blog article?
Exercises like this one are active recall drills — you have to choose the correct term or phrasing yourself, which builds retention faster than passively reading a definition.
Where can I find more vocabulary exercises?
Browse the full Vocabulary exercises hub for hundreds of modules covering Agile, DevOps, security, databases, architecture, and more — organised by IT role and skill.