Database Index Strategy — Vocabulary and Discussion Language
Learn vocabulary for discussing database index types, strategies, and trade-offs: B-tree, composite, partial, and covering indexes.
0 / 18 completed
1 / 18
What is a 'composite index' in database vocabulary?
A composite index (multi-column index) covers multiple columns. Column order matters: a composite index on (last_name, first_name) supports queries filtering on last_name or (last_name, first_name) — but not queries filtering only on first_name (without last_name in the WHERE clause).
2 / 18
What is a 'covering index' (or index-only scan)?
A covering index includes all columns referenced in the query (SELECT, WHERE, ORDER BY, JOIN). This allows an index-only scan — the database never touches the main table heap. PostgreSQL calls this 'Index Only Scan'; it dramatically reduces I/O for read-heavy workloads.
3 / 18
What is 'index bloat' in database optimization vocabulary?
Index bloat occurs when MVCC dead tuples accumulate in index pages after updates/deletes. The index grows in physical size without corresponding live data growth. In PostgreSQL, VACUUM reclaims space but REINDEX is needed to truly compact a bloated index.
4 / 18
What is a 'partial index' in database vocabulary?
A partial index indexes only rows matching a condition: CREATE INDEX ON orders (user_id) WHERE status = 'pending'. This is much smaller than a full index and faster for queries that always filter on status = 'pending' — common for active records vs. archived data.
5 / 18
What is 'index cardinality' in database optimization vocabulary?
Cardinality = the count of distinct values. High cardinality (e.g., user_id with millions of unique values) → high selectivity → index is very effective. Low cardinality (e.g., boolean status column with 2 values) → low selectivity → full table scan may be cheaper than index scan.
6 / 18
Reviewer: 'I'm seeing a full table scan on this query. The `customer_name` column is indexed, but it's not part of the WHERE clause. Maybe we should consider adding an index on `(last_name, first_name)` to improve performance? It seems like the database isn't leveraging our existing index effectively.
Which of the following best describes the reviewer's concern and proposed solution?
The reviewer is pointing out a 'poor index selectivity' issue. A single-column index on just `customer_name` isn't effectively filtering because there are many customers with the same name – it doesn't narrow down the results enough for the database to use it efficiently. The reviewer suggests a composite index, combining `last_name` and `first_name`, which would offer better selectivity and allow the database to quickly isolate specific customer records. Options A and B address incorrect diagnoses; option C is relevant but not the immediate problem here.
7 / 18
During a code review for the `orders` service, Sarah flagged a performance issue. She commented: 'The query to retrieve orders by customer ID is running slowly. We have an index on `customer_id`, but it doesn't seem to be helping much. Perhaps we should investigate if the database is using the index effectively or if there's another column that would benefit from indexing.' What is Sarah primarily concerned about, and what does her comment suggest?
SELECT * FROM orders WHERE customer_id = 123;
Sarah's primary concern is that the database isn't effectively leveraging the existing index on `customer_id`. The comment suggests this might be due to data skew (many customers having the same ID) or because the query optimizer isn't choosing to use the index despite its presence. It's crucial to understand that an index isn't automatically used; the database engine decides based on cost estimations – a misconception is often that an indexed column *always* gets used.
8 / 18
Reviewer: 'I'm seeing a full table scan on this query. The `customer_name` column is indexed, but it's not part of the WHERE clause. Maybe we should consider adding an index on `(last_name, first_name)` to improve performance? It seems like the database isn't leveraging our existing index effectively.
Which of the following best describes the reviewer's concern and proposed solution?
The reviewer is pointing out a 'poor index selectivity' issue. A single-column index on just `customer_name` isn't effectively filtering because there are many customers with the same name – it doesn't narrow down the results enough for the database to use it efficiently. The reviewer suggests a composite index, combining `last_name` and `first_name`, which would offer better selectivity and allow the database to quickly isolate specific customer records. Options A and B address incorrect diagnoses; option C is relevant but not the immediate problem here.
9 / 18
During a code review for the `orders` service, Sarah flagged a performance issue. She commented: 'The query to retrieve orders by customer ID is running slowly. We have an index on `customer_id`, but it doesn't seem to be helping much. Perhaps we should investigate if the database is using the index effectively or if there's another column that would benefit from indexing.' What is Sarah primarily concerned about, and what does her comment suggest?
SELECT * FROM orders WHERE customer_id = 123;
Sarah's primary concern is that the database isn't effectively leveraging the existing index on `customer_id`. The comment suggests this might be due to data skew (many customers having the same ID) or because the query optimizer isn't choosing to use the index despite its presence. It's crucial to understand that an index isn't automatically used; the database engine decides based on cost estimations – a misconception is often that an indexed column *always* gets used.
10 / 18
Reviewer: 'I'm seeing a full table scan on this query. The `customer_name` column is indexed, but it's not part of the WHERE clause. Maybe we should consider adding an index on `(last_name, first_name)` to improve performance? It seems like the database isn't leveraging our existing index effectively.
Which of the following best describes the reviewer's concern and proposed solution?
The reviewer is pointing out a 'poor index selectivity' issue. A single-column index on just `customer_name` isn't effectively filtering because there are many customers with the same name – it doesn't narrow down the results enough for the database to use it efficiently. The reviewer suggests a composite index, combining `last_name` and `first_name`, which would offer better selectivity and allow the database to quickly isolate specific customer records. Options A and B address incorrect diagnoses; option C is relevant but not the immediate problem here.
11 / 18
During a code review for the `orders` service, Sarah flagged a performance issue. She commented: 'The query to retrieve orders by customer ID is running slowly. We have an index on `customer_id`, but it doesn't seem to be helping much. Perhaps we should investigate if the database is using the index effectively or if there's another column that would benefit from indexing.' What is Sarah primarily concerned about, and what does her comment suggest?
SELECT * FROM orders WHERE customer_id = 123;
Sarah's primary concern is that the database isn't effectively leveraging the existing index on `customer_id`. The comment suggests this might be due to data skew (many customers having the same ID) or because the query optimizer isn't choosing to use the index despite its presence. It's crucial to understand that an index isn't automatically used; the database engine decides based on cost estimations – a misconception is often that an indexed column *always* gets used.
12 / 18
Reviewer: 'I'm seeing a full table scan on this query. The `customer_name` column is indexed, but it's not part of the WHERE clause. Maybe we should consider adding an index on `(last_name, first_name)` to improve performance? It seems like the database isn't leveraging our existing index effectively.
Which of the following best describes the reviewer's concern and proposed solution?
The reviewer is pointing out a 'poor index selectivity' issue. A single-column index on just `customer_name` isn't effectively filtering because there are many customers with the same name – it doesn't narrow down the results enough for the database to use it efficiently. The reviewer suggests a composite index, combining `last_name` and `first_name`, which would offer better selectivity and allow the database to quickly isolate specific customer records. Options A and B address incorrect diagnoses; option C is relevant but not the immediate problem here.
13 / 18
During a code review for the `orders` service, Sarah flagged a performance issue. She commented: 'The query to retrieve orders by customer ID is running slowly. We have an index on `customer_id`, but it doesn't seem to be helping much. Perhaps we should investigate if the database is using the index effectively or if there's another column that would benefit from indexing.' What is Sarah primarily concerned about, and what does her comment suggest?
SELECT * FROM orders WHERE customer_id = 123;
Sarah's primary concern is that the database isn't effectively leveraging the existing index on `customer_id`. The comment suggests this might be due to data skew (many customers having the same ID) or because the query optimizer isn't choosing to use the index despite its presence. It's crucial to understand that an index isn't automatically used; the database engine decides based on cost estimations – a misconception is often that an indexed column *always* gets used.
14 / 18
During a standup meeting, Mark mentioned that the query retrieving recent user activity was consistently slow. He stated: 'We have an index on `last_login` but it's not fully utilizing its potential.' Which of the following best describes Mark's concern regarding index strategy?
Mark is highlighting the importance of *index cardinality*. This refers to how much of a table's data an index can satisfy. A low cardinality means the index isn't effectively filtering rows and causing the database to read more data than necessary.
Option B is incorrect as 'covering' indicates minimal data reads, not necessarily high cardinality. Option C misinterprets 'index bloat'; it's about index size, not utilization. Option D suggests a query issue, which wasn't the core of Mark's statement.
15 / 18
Reviewer Alex flagged a performance bottleneck in the API endpoint for retrieving product details. The database uses the following query: `SELECT * FROM products WHERE product_name = 'Widget' AND category_id = 5;`. The `product_name` column is indexed, but not the `category_id` column. What optimization technique could Alex suggest to improve this query's performance?
A composite index – an index on multiple columns – is the correct solution. This allows the database to efficiently filter based on *both* criteria in the query.
Option B would be ineffective because it only covers one part of the `WHERE` clause. Option C addresses database server performance, not query optimization. Option D is a maintenance command and doesn't directly solve the indexing problem.
16 / 18
During a code review, developer David pointed out that the `orders` table had a large number of indexes. He proposed running a command to identify and remove unused indexes. Which database command best aligns with David's suggestion?
The command `OPTIMIZE TABLE orders` is the most appropriate. It's specifically designed to reclaim space occupied by fragmented indexes and rebuild them for optimal performance.
Analyze updates statistics; reindex rebuilds an index from scratch – which isn't needed when the index remains valid but unused. `ALTER TABLE...DROP INDEX` is a more drastic approach, removing all indexes.
17 / 18
A senior developer, Sarah, is investigating slow performance in a reporting query that retrieves customer purchase history. The database schema includes an index on `customer_id` and `order_date`. She suspects the index isn't being used effectively. What metric should she investigate to understand potential *index cardinality*?
*Index cardinality* refers to the number of distinct values represented by an index. A low cardinality means the index covers a small subset of the table's rows – making it less effective for filtering large datasets.
Option A measures index size; Option C describes how the date column is used, not its cardinality. Option D is related to server performance and doesn't directly address index effectiveness.
18 / 18
During a Slack conversation about database query optimization, a developer asked: 'What's the difference between a 'covering index' and a regular index?' Which of the following best describes a covering index?
A 'covering index' is an index that contains all the columns needed to satisfy a particular query. This allows the database to retrieve all necessary data directly from the index itself, without needing to access the base table – avoiding a costly lookup.
Option A describes a composite index; Option C misrepresents the purpose of an index; and Option D is incorrect as covering indexes are about efficiency, not duplication.
What does the "Database Index Strategy — Vocabulary and Discussion Language" exercise practise?
Learn vocabulary for discussing database index types, strategies, and trade-offs: B-tree, composite, partial, and covering indexes.
How many questions are in this exercise?
This exercise has 18 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database Optimization category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Database Index Strategy — Vocabulary and Discussion Language" part of a larger series?
Yes — it's one exercise in the Database Optimization category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database Optimization category page for related exercises, or browse the main Exercises hub for other IT English topics.