Indexing Strategy Discussion Language
Practise vocabulary for index design discussions: B-tree vs. hash, composite indexes, covering indexes, partial indexes, and index trade-offs.
0 / 26 completed
1 / 26
A ___ index is the default type in most relational databases, efficient for range queries and ORDER BY operations.
B-tree (balanced tree) indexes support equality lookups, range queries (BETWEEN, >, <), and ORDER BY efficiently. They're the default index type in PostgreSQL, MySQL, and SQL Server.
2 / 26
A ___ index on (last_name, first_name) can serve queries filtering on last_name alone, but cannot efficiently serve queries on first_name alone.
A composite index (multi-column index) follows the leftmost prefix rule: it can serve queries using the leading columns in order. (last_name, first_name) works for last_name but not first_name in isolation.
3 / 26
A ___ index only covers a subset of rows — for example, indexing only rows where status = 'active'.
A partial index (PostgreSQL) includes only rows matching a WHERE condition. It's smaller and faster than a full-table index — ideal when queries frequently filter on a selective condition like 'status = active'.
4 / 26
Every index improves ___ performance but degrades ___ performance, since each index must be updated on every write.
Indexes speed up read queries (lookup, range scan) but add overhead to writes (INSERT, UPDATE, DELETE must update all indexes). Over-indexing a write-heavy table degrades throughput — index choice is always a trade-off.
5 / 26
'Table bloat' in the context of indexing refers to ___.
Table and index bloat accumulates dead rows (in PostgreSQL: from MVCC, old versions kept until VACUUM) and dead index entries. VACUUM reclaims this space. Heavy UPDATE/DELETE workloads require regular maintenance to prevent bloat.
6 / 26
Sarah: 'I'm seeing really slow performance on this API endpoint. It's taking almost a second to return the user data, and I suspect it's related to indexing. Mark: 'Maybe we should just add an index to the user_id column?'This scenario highlights a common issue: blindly adding indexes doesn't always solve performance problems. The correct response acknowledges the need for further investigation – specifically analyzing the query execution plan (
EXPLAIN) to identify *which* indexes are being used and *why*. Adding an index on email is more likely to address the slow query, as it's probably a common filter criteria. Simply adding an index to the primary key (user_id) may not be optimal if the query isn't using that specific index.7 / 26
Mark in the Slack message mentioned adding an index to `user_id`. However, during a code review of the new API endpoint, David comments: 'I'm concerned about indexing *all* the columns in the `users` table. We're dealing with millions of records, and creating too many indexes could significantly impact write performance. What's the most appropriate response to David from your perspective as a senior developer?', The key here is balancing read and write performance. While indexing improves query speed, excessive indexing dramatically slows down writes (inserts, updates, deletes) because every index needs updating. David's concern is valid – a blanket approach to indexing can lead to severe performance degradation. The correct response acknowledges his point and advocates for a targeted, data-driven strategy rather than blindly adding indexes.
8 / 26
PR Description:
"Fix: Added an index to the `orders` table on `customer_id`. This should improve query performance for reporting on customer order trends. The index was created using a B-tree structure."Option A is incorrect because B-tree indexes are generally good for many query types but aren't always optimal. Option C highlights a valid concern – the description lacks crucial context about index selection and potential trade-offs. The correct answer, Option B, acknowledges the core purpose of the index (performance) and correctly identifies the B-tree structure, while also hinting at the important consideration of write performance.
9 / 26
Mark (your team lead) responds: 'Let's first analyze our query patterns. Are there specific columns that are consistently used in WHERE clauses or JOIN conditions?' During this analysis, the team identifies a frequent query that filters users by their `email` address and includes a sort on `registration_date`. Considering this information, which of the following actions would be MOST appropriate to discuss with the remaining developers before implementing an index?
Option A: Immediately create a composite index on
(email, registration_date). This will undoubtedly optimize the query and prevent future performance issues.
Option B: Create separate indexes on
email and registration_date individually. This offers maximum flexibility for different query requirements and minimizes index bloat.
Option C: Conduct a thorough profiling exercise to identify the actual execution plan of the slow query, before considering any indexing changes. Understanding how the database is *already* processing the query is crucial.
Option D: Immediately create an index on
user_id as it's a common join column and should always be indexed for optimal performance.The correct answer (Option B) prioritizes understanding the specific query needs. While indexing `user_id` might seem logical due to its presence in JOIN conditions, analyzing the dominant query pattern – filtering by email and sorting by registration date – is paramount. Creating a composite index on those two columns will directly address the identified bottleneck. Option A could lead to unnecessary index bloat if other queries don't benefit from the combined index; Option C emphasizes data-driven decision making, which is best practice for indexing strategies, and Option D represents a potentially misguided approach based solely on common join column presence.
10 / 26
Sarah: 'I'm seeing really slow performance on this API endpoint. It's taking almost a second to return the user data, and I suspect it's related to indexing. Mark: 'Maybe we should just add an index to the user_id column?'This scenario highlights a common issue: blindly adding indexes doesn't always solve performance problems. The correct response acknowledges the need for further investigation – specifically analyzing the query execution plan (
EXPLAIN) to identify *which* indexes are being used and *why*. Adding an index on email is more likely to address the slow query, as it's probably a common filter criteria. Simply adding an index to the primary key (user_id) may not be optimal if the query isn't using that specific index.11 / 26
Mark in the Slack message mentioned adding an index to `user_id`. However, during a code review of the new API endpoint, David comments: 'I'm concerned about indexing *all* the columns in the `users` table. We're dealing with millions of records, and creating too many indexes could significantly impact write performance. What's the most appropriate response to David from your perspective as a senior developer?', The key here is balancing read and write performance. While indexing improves query speed, excessive indexing dramatically slows down writes (inserts, updates, deletes) because every index needs updating. David's concern is valid – a blanket approach to indexing can lead to severe performance degradation. The correct response acknowledges his point and advocates for a targeted, data-driven strategy rather than blindly adding indexes.
12 / 26
PR Description:
"Fix: Added an index to the `orders` table on `customer_id`. This should improve query performance for reporting on customer order trends. The index was created using a B-tree structure."Option A is incorrect because B-tree indexes are generally good for many query types but aren't always optimal. Option C highlights a valid concern – the description lacks crucial context about index selection and potential trade-offs. The correct answer, Option B, acknowledges the core purpose of the index (performance) and correctly identifies the B-tree structure, while also hinting at the important consideration of write performance.
13 / 26
Mark (your team lead) responds: 'Let's first analyze our query patterns. Are there specific columns that are consistently used in WHERE clauses or JOIN conditions?' During this analysis, the team identifies a frequent query that filters users by their `email` address and includes a sort on `registration_date`. Considering this information, which of the following actions would be MOST appropriate to discuss with the remaining developers before implementing an index?
Option A: Immediately create a composite index on
(email, registration_date). This will undoubtedly optimize the query and prevent future performance issues.
Option B: Create separate indexes on
email and registration_date individually. This offers maximum flexibility for different query requirements and minimizes index bloat.
Option C: Conduct a thorough profiling exercise to identify the actual execution plan of the slow query, before considering any indexing changes. Understanding how the database is *already* processing the query is crucial.
Option D: Immediately create an index on
user_id as it's a common join column and should always be indexed for optimal performance.The correct answer (Option B) prioritizes understanding the specific query needs. While indexing `user_id` might seem logical due to its presence in JOIN conditions, analyzing the dominant query pattern – filtering by email and sorting by registration date – is paramount. Creating a composite index on those two columns will directly address the identified bottleneck. Option A could lead to unnecessary index bloat if other queries don't benefit from the combined index; Option C emphasizes data-driven decision making, which is best practice for indexing strategies, and Option D represents a potentially misguided approach based solely on common join column presence.
14 / 26
Sarah: 'I'm seeing really slow performance on this API endpoint. It's taking almost a second to return the user data, and I suspect it's related to indexing. Mark: 'Maybe we should just add an index to the user_id column?'This scenario highlights a common issue: blindly adding indexes doesn't always solve performance problems. The correct response acknowledges the need for further investigation – specifically analyzing the query execution plan (
EXPLAIN) to identify *which* indexes are being used and *why*. Adding an index on email is more likely to address the slow query, as it's probably a common filter criteria. Simply adding an index to the primary key (user_id) may not be optimal if the query isn't using that specific index.15 / 26
Mark in the Slack message mentioned adding an index to `user_id`. However, during a code review of the new API endpoint, David comments: 'I'm concerned about indexing *all* the columns in the `users` table. We're dealing with millions of records, and creating too many indexes could significantly impact write performance. What's the most appropriate response to David from your perspective as a senior developer?', The key here is balancing read and write performance. While indexing improves query speed, excessive indexing dramatically slows down writes (inserts, updates, deletes) because every index needs updating. David's concern is valid – a blanket approach to indexing can lead to severe performance degradation. The correct response acknowledges his point and advocates for a targeted, data-driven strategy rather than blindly adding indexes.
16 / 26
PR Description:
"Fix: Added an index to the `orders` table on `customer_id`. This should improve query performance for reporting on customer order trends. The index was created using a B-tree structure."Option A is incorrect because B-tree indexes are generally good for many query types but aren't always optimal. Option C highlights a valid concern – the description lacks crucial context about index selection and potential trade-offs. The correct answer, Option B, acknowledges the core purpose of the index (performance) and correctly identifies the B-tree structure, while also hinting at the important consideration of write performance.
17 / 26
Mark (your team lead) responds: 'Let's first analyze our query patterns. Are there specific columns that are consistently used in WHERE clauses or JOIN conditions?' During this analysis, the team identifies a frequent query that filters users by their `email` address and includes a sort on `registration_date`. Considering this information, which of the following actions would be MOST appropriate to discuss with the remaining developers before implementing an index?
Option A: Immediately create a composite index on
(email, registration_date). This will undoubtedly optimize the query and prevent future performance issues.
Option B: Create separate indexes on
email and registration_date individually. This offers maximum flexibility for different query requirements and minimizes index bloat.
Option C: Conduct a thorough profiling exercise to identify the actual execution plan of the slow query, before considering any indexing changes. Understanding how the database is *already* processing the query is crucial.
Option D: Immediately create an index on
user_id as it's a common join column and should always be indexed for optimal performance.The correct answer (Option B) prioritizes understanding the specific query needs. While indexing `user_id` might seem logical due to its presence in JOIN conditions, analyzing the dominant query pattern – filtering by email and sorting by registration date – is paramount. Creating a composite index on those two columns will directly address the identified bottleneck. Option A could lead to unnecessary index bloat if other queries don't benefit from the combined index; Option C emphasizes data-driven decision making, which is best practice for indexing strategies, and Option D represents a potentially misguided approach based solely on common join column presence.
18 / 26
Sarah: 'I'm seeing really slow performance on this API endpoint. It's taking almost a second to return the user data, and I suspect it's related to indexing. Mark: 'Maybe we should just add an index to the user_id column?'This scenario highlights a common issue: blindly adding indexes doesn't always solve performance problems. The correct response acknowledges the need for further investigation – specifically analyzing the query execution plan (
EXPLAIN) to identify *which* indexes are being used and *why*. Adding an index on email is more likely to address the slow query, as it's probably a common filter criteria. Simply adding an index to the primary key (user_id) may not be optimal if the query isn't using that specific index.19 / 26
Mark in the Slack message mentioned adding an index to `user_id`. However, during a code review of the new API endpoint, David comments: 'I'm concerned about indexing *all* the columns in the `users` table. We're dealing with millions of records, and creating too many indexes could significantly impact write performance. What's the most appropriate response to David from your perspective as a senior developer?', The key here is balancing read and write performance. While indexing improves query speed, excessive indexing dramatically slows down writes (inserts, updates, deletes) because every index needs updating. David's concern is valid – a blanket approach to indexing can lead to severe performance degradation. The correct response acknowledges his point and advocates for a targeted, data-driven strategy rather than blindly adding indexes.
20 / 26
PR Description:
"Fix: Added an index to the `orders` table on `customer_id`. This should improve query performance for reporting on customer order trends. The index was created using a B-tree structure."Option A is incorrect because B-tree indexes are generally good for many query types but aren't always optimal. Option C highlights a valid concern – the description lacks crucial context about index selection and potential trade-offs. The correct answer, Option B, acknowledges the core purpose of the index (performance) and correctly identifies the B-tree structure, while also hinting at the important consideration of write performance.
21 / 26
Mark (your team lead) responds: 'Let's first analyze our query patterns. Are there specific columns that are consistently used in WHERE clauses or JOIN conditions?' During this analysis, the team identifies a frequent query that filters users by their `email` address and includes a sort on `registration_date`. Considering this information, which of the following actions would be MOST appropriate to discuss with the remaining developers before implementing an index?
Option A: Immediately create a composite index on
(email, registration_date). This will undoubtedly optimize the query and prevent future performance issues.
Option B: Create separate indexes on
email and registration_date individually. This offers maximum flexibility for different query requirements and minimizes index bloat.
Option C: Conduct a thorough profiling exercise to identify the actual execution plan of the slow query, before considering any indexing changes. Understanding how the database is *already* processing the query is crucial.
Option D: Immediately create an index on
user_id as it's a common join column and should always be indexed for optimal performance.The correct answer (Option B) prioritizes understanding the specific query needs. While indexing `user_id` might seem logical due to its presence in JOIN conditions, analyzing the dominant query pattern – filtering by email and sorting by registration date – is paramount. Creating a composite index on those two columns will directly address the identified bottleneck. Option A could lead to unnecessary index bloat if other queries don't benefit from the combined index; Option C emphasizes data-driven decision making, which is best practice for indexing strategies, and Option D represents a potentially misguided approach based solely on common join column presence.
22 / 26
During a code review of the new `get_user` API endpoint, Emily comments: 'I'm worried this index on `user_id` isn't covering all the common query patterns. We should consider if there are other fields frequently used in filters.' David replies: 'That's a good point; let's examine our logging data to see which columns are most often queried alongside `user_id`.' What is the *primary* purpose of David's response?
The core issue is whether the existing index is sufficient. David's response directly addresses this by advocating for data analysis (logging) to understand *how* the `user_id` is actually being used in queries. This prevents blindly adding indexes and wasting resources; it's a crucial step before committing to an indexing strategy.
23 / 26
Alex writes in the team Slack channel: 'I'm seeing really slow response times on the `/orders` API. I suspect it's related to the lack of an index on `order_date`. Could you help me evaluate if this is a valid concern?' What should Mark, the senior developer, *immediately* do?
The most effective initial step is to gather more information from Alex. Simply adding an index based on a suspicion isn't good practice. Understanding the queries and response times provides context crucial for determining if indexing is the root cause of the performance issue. It's about investigation before remediation.
24 / 26
Reviewing a PR description:
'Enhancement: Added an index to the `products` table on `category_id`. This should improve query performance when retrieving products by category. The index is a clustered B-tree.' Which statement best reflects the *underlying rationale* for this change?
PR descriptions should clearly articulate *why* a change was made. This description explicitly mentions a 'performance bottleneck' related to category retrieval – this is the key justification for creating the index. A good PR description explains the problem and the solution's purpose.
25 / 26
"During today's stand-up, I mentioned we were exploring adding an index to the `customer_segment` column in the `customers` table. However, during a follow-up discussion with the data team, they raised concerns about potential cardinality issues and suggested analyzing the distribution of values before proceeding." What does this scenario primarily highlight regarding indexing strategy?
This scenario demonstrates a key principle: blindly adding indexes can be detrimental. Cardinality refers to the number of distinct values in a column. High-cardinality columns benefit greatly from indexing, while low-cardinality columns might actually *hurt* performance due to index fragmentation and increased storage costs. Data analysis is critical.
26 / 26
The following API response represents a query against an e-commerce database:
```json
{
"status": "success",
"data": {
"products": [
{"product_id": 123, "name": "Laptop", "category_id": 4},
{"product_id": 456, "name": "Mouse", "category_id": 4}
]
}
}
```
Assuming a user is frequently retrieving products by `category_id`, which action would *most* likely benefit the system's performance?
The API response demonstrates that queries are often filtered by `category_id`. While indexes can help, optimizing the *query* – retrieving only the needed products based on category – is typically the most effective initial step. An index would provide speed but wouldn't address a poorly written query.
Frequently Asked Questions
What does the "Indexing Strategy Discussion Language" exercise practise?
Practise vocabulary for index design discussions: B-tree vs. hash, composite indexes, covering indexes, partial indexes, and index trade-offs.
How many questions are in this exercise?
This exercise has 26 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 Schema Design 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 "Indexing Strategy Discussion Language" part of a larger series?
Yes — it's one exercise in the Database Schema Design 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 Schema Design category page for related exercises, or browse the main Exercises hub for other IT English topics.