SQL Quiz: Indexes For Performance
10 questions · exam conditions
0:00
Indexes For PerformanceQuestion 1 of 10

A database has Customers(customer_id, email, ...) and Orders(order_id, customer_id, status, ...). The primary keys are indexed, and Customers.email has a unique index. Orders contains 80 million rows and has no index on customer_id. A common query finds one customer by email and joins that customer to all matching orders.

Which additional index would most directly improve the join after the customer has been found?

Create an index on Orders(customer_id) so matching child rows can be located directly.
Create another index on Customers(customer_id) so the parent row can be validated twice.
Create an index on Orders(status) so orders can be grouped before evaluating the join.
Create another index on Customers(email) so the initial lookup has two access paths.
← Back to quizzes

SQL Quiz

SQL Quiz: Indexes For Performance

Practice Indexes For Performance in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Indexes For Performance, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A database has Customers(customer_id, email, ...) and Orders(order_id, customer_id, status, ...). The primary keys are indexed, and Customers.email has a unique index. Orders contains 80 million rows and has no index on customer_id. A common query finds one customer by email and joins that customer to all matching orders.

Which additional index would most directly improve the join after the customer has been found?

  1. Create an index on Orders(customer_id) so matching child rows can be located directly. (correct answer)
  2. Create another index on Customers(customer_id) so the parent row can be validated twice.
  3. Create an index on Orders(status) so orders can be grouped before evaluating the join.
  4. Create another index on Customers(email) so the initial lookup has two access paths.
Explanation: When optimizing a multi-table query, think about where the bottleneck occurs at each stage. The query first finds one customer via the email index — that part is already fast. The slow part is what happens next: retrieving all orders for that customer from an 80-million-row table with no index on customer_id. Without an index, the database must scan every single row to find the matches. This is exactly why A is correct. Adding an index on Orders(customer_id) gives the query engine a direct path to only the rows belonging to that customer. Instead of a full table scan across 80 million rows, the database performs an index lookup and fetches a small subset — a dramatic improvement precisely where the join needs it. B is a trap based on misunderstanding indexing fundamentals. Customers.customer_id is already the primary key, which is automatically indexed. Adding a second index on it provides no benefit — the optimizer already has a fast path to the parent row. C sounds plausible but targets the wrong column. An index on Orders(status) helps queries that filter by status, not queries that join on customer_id. It does nothing to speed up locating which orders belong to a specific customer. D similarly misidentifies where the problem is. Customers(email) already has a unique index per the problem statement, so a duplicate index is redundant and wasteful. Study tip: On index optimization questions, trace the query step by step and ask, "Which column is used to look up rows at the slowest step?" That column is your indexing target.

Question 2

A sales report filters with region = ?, sales_rep_id = ?, and a range on order_date. The current B-tree index is (region, order_date, sales_rep_id). Each region contains many sales representatives, and the date range often covers several months.

Which index design would usually narrow the search more effectively for this exact predicate pattern?

  1. Use (order_date, region, sales_rep_id) so the range predicate determines the first index position.
  2. Keep (region, order_date, sales_rep_id) because including all predicates makes key order irrelevant.
  3. Use (sales_rep_id) alone because the most selective predicate should always be the only key.
  4. Use (region, sales_rep_id, order_date) so equality keys precede the date range key. (correct answer)
Explanation: When designing a B-tree index for mixed predicates, you need to apply the left-prefix rule: equality predicates should come before range predicates in the index column order. This is because a B-tree can only efficiently continue scanning along columns to the right of a range condition — once a range is encountered, columns after it cannot be used to narrow the search further. In this query, region = ? and sales_rep_id = ? are both equality predicates, while order_date is a range predicate. That means the ideal index places both equality columns first, then the range column last. Option D, (region, sales_rep_id, order_date), follows this pattern exactly. The index first pins down a specific region, then a specific sales rep within that region, then scans only the relevant date range — producing a very tight, efficient seek. Option A is flawed because placing order_date first forces the index to scan a broad date range before filtering by region or rep, which is far less selective as an entry point. Option B is a misconception — key order absolutely matters in a B-tree index. Including all predicates does not make ordering irrelevant; a poorly ordered index still wastes I/O. Option C is an oversimplification. Indexing only sales_rep_id ignores the region and date predicates entirely, and a single-column index rarely outperforms a well-composed composite index for multi-predicate queries. As a study tip, remember this rule: equality columns first, range columns last. When you see a query mixing = and range conditions (BETWEEN, <, >), mentally sort the index columns that way before evaluating the answer choices.

Question 3

A database supports included columns. Orders has an index on customer_id. A frequent query filters for one customer and returns order_date and total_amount. Some customers have hundreds of thousands of orders, and the plan performs many lookups from the index into the base table.

Which index change is most likely to reduce those base-table lookups while preserving the useful filter key?

  1. Use total_amount as the key and include customer_id and order_date in the index.
  2. Use order_date as the key and include customer_id and total_amount in the index.
  3. Use customer_id as the key and include order_date and total_amount in the index. (correct answer)
  4. Keep only the current key because selected columns cannot affect index-based query performance.
Explanation: When a query needs columns that aren't part of the index key, the database must perform a "lookup" — jumping from the index leaf back to the base table row to retrieve missing data. With hundreds of thousands of orders per customer, those lookups become extremely expensive. The solution is a covering index: an index that contains every column the query needs, so the database never has to touch the base table. Option C is correct because it keeps customer_id as the leading key column — exactly what the WHERE customer_id = ? filter needs for efficient range scans — while including order_date and total_amount as non-key columns. The query can now be satisfied entirely from the index, eliminating those costly base-table lookups without changing the filter behavior at all. Option A makes total_amount the key, which destroys the ability to efficiently filter by customer_id. The index would no longer support that equality predicate efficiently, forcing a scan rather than a seek. Option B has the same fundamental problem — using order_date as the key means the index isn't organized by customer_id, so filtering for a specific customer requires scanning the whole index rather than seeking to one spot. Option D is simply wrong; included columns are specifically designed to affect query performance by enabling covering indexes — this is one of the most impactful index tuning techniques available. A useful rule of thumb: the key handles filtering and ordering; included columns handle retrieval. When you spot a query with a clear filter predicate and a fixed set of returned columns, ask yourself whether adding those returned columns as includes could create a covering index.

Question 4

A database supports filtered or partial indexes. In a 100-million-row Subscriptions table, about one percent of rows have status = 'ACTIVE'. The dominant query uses WHERE status = 'ACTIVE' AND account_id = ?. Inactive rows are rarely queried.

Which index is likely to provide the smallest effective access path for this workload?

  1. A full-table index on (status) because the status column has only a few distinct values.
  2. A partial index on (account_id) containing only rows where status = 'ACTIVE'. (correct answer)
  3. A partial index on (status) containing only rows where account_id is not null.
  4. A full-table index on (account_id) that deliberately excludes the status condition.
Explanation: When a query filters on multiple columns, the ideal index should match the query's shape as precisely as possible — minimizing both the number of index rows scanned and the number of table rows visited. Here, your dominant query is WHERE status = 'ACTIVE' AND account_id = ?. Since only ~1% of 100 million rows are ACTIVE, a partial index that physically excludes inactive rows contains just ~1 million entries instead of 100 million. Option B — a partial index on (account_id) filtered to status = 'ACTIVE' rows only — is exactly this. The database can seek directly to the specific account_id value within an already-tiny index, making the access path extremely tight. Option A fails because indexing (status) alone still returns ~1 million rows matching ACTIVE, all of which must then be filtered by account_id. Low-cardinality columns like status make poor standalone index keys precisely because they resolve poorly to individual rows. Option C is a trap: a partial index on (status) filtered by account_id IS NOT NULL doesn't help you look up a specific account efficiently — you're still scanning by a low-cardinality column, just in a smaller structure. Option D, a full-table index on (account_id), lets you find a specific account quickly, but the index still contains all 100 million rows. The database must then check status against each match, and the index footprint is far larger than necessary. A useful rule of thumb: partial indexes pay off when a query consistently targets a small, well-defined subset of rows. Always ask whether the WHERE clause in your index definition mirrors the WHERE clause in your queries.

Question 5

An Invoices table contains 30 million rows. The region column has five values distributed nearly evenly, while invoice_number is unique for almost every row. A frequent query filters on both columns: WHERE region = ? AND invoice_number = ?. Only one new single-column index may be created.

Which index is most likely to reduce the number of rows examined for this query?

  1. Create an index on invoice_number, because that predicate identifies very few candidate rows. (correct answer)
  2. Create an index on region, because it appears first in the query's filter conditions.
  3. Create an index on the table's row count, because the table is substantially larger than memory.
  4. Create an index on a frequently selected output column, because it can reduce predicate evaluation.
Explanation: When choosing an index to reduce rows examined, the key concept is selectivity — how effectively a predicate narrows down the candidate rows. A highly selective index filters out most rows immediately, leaving the database engine very little work to do. Since invoice_number is unique across nearly 30 million rows, filtering on it returns approximately one row. An index on invoice_number lets the database jump directly to that single candidate, making the region filter almost trivially cheap to evaluate afterward. This makes A the correct choice — it exploits maximum selectivity. B is wrong because region has only five evenly distributed values, meaning each region matches roughly 30,000,0005=6,000,000\frac{30{,}000{,}000}{5} = 6{,}000{,}000 rows. An index on region would still leave the engine examining millions of candidates. The fact that region appears first in the WHERE clause is irrelevant — SQL optimizers don't honor predicate order; they evaluate based on cost estimates and statistics. C describes something that doesn't exist. You cannot create an index "on the row count." This is a nonsense distractor designed to catch students who confuse database statistics (which track table size and distribution) with actual indexable columns. D confuses index types. Covering indexes can avoid full row lookups by including output columns, but that's a different optimization entirely. Indexing an output column does nothing to accelerate predicate filtering unless that column is itself part of the WHERE clause. The study tip here: always estimate selectivity first. When you see index design questions, mentally calculate how many rows each predicate returns — the predicate that leaves the fewest rows wins.

Question 6

Two large tables are joined on indexed columns. The query has no filters, returns most columns, and is expected to match nearly every row from each table. A developer claims that the indexes guarantee an index-based join will outperform table scans.

Which assessment of the developer's claim is most accurate?

  1. The claim is false because indexes can improve filters but can never improve joins between large tables.
  2. The claim is guaranteed because any equality join on two indexed columns must use index seeks.
  3. The claim is guaranteed only when both indexes contain unique values on their join columns.
  4. The claim is not guaranteed; scans with a hash or similar join may be cheaper when most rows are needed. (correct answer)
Explanation: When evaluating whether an index will improve query performance, you need to think about selectivity and join strategy together. Indexes shine when they help the database skip large portions of data — but when a query needs nearly every row, that advantage disappears. This is exactly why D is correct. When a join matches almost all rows from both large tables, the query engine must process an enormous result set regardless of the access method. Reading index pages plus data pages often costs more I/O than a straight sequential table scan. Modern optimizers recognize this and will typically choose a hash join or merge join paired with full scans, which move through data more efficiently in bulk. The existence of an index doesn't obligate the optimizer to use it — it chooses based on estimated cost. A is wrong because it overcorrects. Indexes absolutely can improve joins — just not in every scenario. When selectivity is high and only a small fraction of rows match, index-based nested loop joins can be dramatically faster. The claim in A is too absolute. B is wrong because it confuses the availability of an index with the optimizer's obligation to use it. Equality joins on indexed columns don't guarantee index seeks; the optimizer may still choose scans if the cost estimate favors them. C is wrong because uniqueness is relevant to index efficiency in some contexts, but it doesn't override the fundamental cost calculation when most rows are being returned. Study tip: Whenever you see a question about indexes and performance, ask yourself: how selective is this query? High selectivity favors indexes; low selectivity (most rows returned) often favors scans.

Question 7

A conventional B-tree index exists on Transactions(tenant_id, customer_id, transaction_date). The optimizer does not use index skip scans. A new report filters by customer_id and a narrow transaction_date range but does not supply tenant_id.

Which change is most likely to provide an efficient index seek for the report?

  1. Add an index on (customer_id, transaction_date) to match the supplied equality and range predicates. (correct answer)
  2. Keep the existing index because every filtered column appears somewhere within its key.
  3. Add an index on (transaction_date, tenant_id) because a date range always belongs first.
  4. Add an index on (tenant_id, transaction_date) because retaining the original leading key is required.
Explanation: When designing indexes for query performance, the most critical concept is the leading column rule: a B-tree index can only be used efficiently for a query if the query supplies predicates starting from the leftmost key column. Think of an index like a phone book — if you don't know the last name, alphabetical order by last name doesn't help you find someone by first name alone. The report filters on customer_id and transaction_date but omits tenant_id, which is the leading column of the existing index on (tenant_id, customer_id, transaction_date). Without a value for tenant_id, the optimizer cannot perform an index seek — it would need to scan the entire index. The fix is straightforward: create a new index where the columns the query actually supplies come first. An index on (customer_id, transaction_date) lets the optimizer seek directly to matching customer_id values and then range-scan within the narrow date window. That makes A the correct answer. B is wrong because "the column appears somewhere in the index" is not sufficient — position matters. A column buried after an unsupplied leading key is inaccessible without a full scan. C is wrong because the assumption that a date range "always belongs first" is a myth; leading columns should be equality predicates first, range predicates last, and neither tenant_id nor skipping customer_id helps this query. D is wrong for the same root reason as the original problem — retaining tenant_id as the leading key still blocks an efficient seek when tenant_id is absent. Your study tip: always match index column order to query predicate order — equality columns first, range columns last, and never lead with a column the query skips.

Question 8

An Events table has an index on event_timestamp. A query uses WHERE DATE(event_timestamp) = '2026-06-15'. The database does not have a matching expression index, and the execution plan scans most of the index rather than seeking to the relevant day.

Which revision is most likely to let the existing index support an efficient range lookup?

  1. Filter from the start of the date through, but not including, the start of the next date. (correct answer)
  2. Convert the date literal to text before comparing it with the timestamp expression.
  3. Apply DATE to the timestamp twice so the optimizer can recognize the intended day.
  4. Move the DATE expression later in the WHERE clause so the index condition appears first.
Explanation: When a query wraps a column inside a function like DATE(event_timestamp), the database can no longer use a standard B-tree index on that column to seek directly to matching rows — it must evaluate the function for every row it examines, effectively neutralizing the index. This is one of the most common query performance pitfalls in SQL. The fix is to rewrite the condition so the raw column is compared against literal boundary values, enabling a range scan. For a single day, that means: WHERE event_timestamp >= '2026-06-15 00:00:00' AND event_timestamp < '2026-06-16 00:00:00'. Now the optimizer can seek to the start of June 15th and stop at the start of June 16th — exactly what answer A describes. The existing index on event_timestamp can serve this range lookup efficiently without any schema changes. Answer B is wrong because converting the date literal to text makes the comparison less precise and doesn't resolve the fundamental problem — the function is still applied to the indexed column, and string-to-timestamp comparisons can introduce type mismatch errors or incorrect results. Answer C is wrong because applying DATE twice to the column only doubles the function overhead; it doesn't help the optimizer recognize a sargable condition. There is no mechanism by which redundant wrapping reveals intent. Answer D is wrong because clause ordering within WHERE does not affect index usage in modern SQL optimizers. The optimizer evaluates all conditions logically, regardless of their physical position. Study tip: Any time you see a function applied directly to an indexed column in a WHERE clause, ask yourself: "Can I rewrite this as a range on the raw column?" That rewrite almost always restores index efficiency.

Question 9

Orders already has a B-tree index on (customer_id, created_at). A common query joins Customers to Orders on customer_id and restricts the operation to one customer. A proposal suggests adding a separate index on Orders(customer_id) solely for this query.

What is the most appropriate initial assessment of the proposal?

  1. The separate index is required because composite indexes can only be used when every key column appears in the query predicate.
  2. The existing composite index already supports a seek on its leading customer_id key for this join, making the new index redundant. (correct answer)
  3. The separate index is required because join predicates on a foreign key cannot use the prefix of a composite index.
  4. The existing index is useful here only when created_at is also supplied as a filter predicate alongside customer_id.
Explanation: When evaluating index proposals, the key question is always: does an existing index already cover the access pattern? For B-tree indexes specifically, you need to understand the concept of a prefix seek — a database engine can use the leftmost columns of a composite index independently, without requiring all columns to be present in the query. A composite index on (customer_id, created_at) is physically sorted first by customer_id, then by created_at within each customer. This means the engine can seek directly to all rows matching a specific customer_id using only the index's leading column — exactly what a join on customer_id needs. The existing index already handles this efficiently, making the proposed Orders(customer_id) index redundant. That's why B is correct. A is wrong because it describes the exact opposite of how B-tree indexes work. You do not need every key column in your predicate — using just the leading prefix is a fundamental and intentional feature of composite indexes. C is wrong because it invents a restriction that doesn't exist. Join predicates behave just like equality filters for index-seeking purposes; there is no special rule that blocks composite index prefixes from being used in joins. D is wrong because it confuses when the full composite index helps versus when any part of it helps. The engine needs created_at only when you want to narrow results within a customer — the customer_id seek alone works fine without it. Study tip: Always check the leftmost columns of existing indexes before proposing new ones — prefix seeks are free, and duplicate indexes waste write performance.

Question 10

Customers.customer_id is an integer primary key. A large imported Events table stores the corresponding value in a text column named customer_id_text, which has a B-tree index. A join converts Events.customer_id_text to an integer before comparing it with Customers.customer_id. The plan scans the large Events table because the conversion is applied to its indexed column.

Which long-term change is most likely to make the join index-friendly and reliable?

  1. Convert the customer primary key to text within every join and remove its existing index.
  2. Keep the mismatched types and add another identical index on the text event column.
  3. Store compatible customer ID types in both tables and index the event-side join column. (correct answer)
  4. Move the conversion to the SELECT list while leaving the mismatched join comparison unchanged.
Explanation: When a database engine applies a function or type conversion to an indexed column inside a join condition, it can no longer use that index for a seek — it must scan every row instead. This is called a non-sargable condition, and recognizing it is the key to answering questions like this one. The root problem here is a type mismatch: Customers.customer_id is an integer, but Events.customer_id_text is text. The database must convert one side before comparing them. When that conversion wraps the indexed Events column, the index becomes useless. The long-term fix, which is answer C, is to align the data types at the schema level — store the customer ID as the same type in both tables — and then index that column. With compatible types, the join comparison is direct, the index is fully usable, and you eliminate an implicit conversion that could also introduce subtle data bugs. Answer A is backwards: converting the primary key to text pushes the problem to the other side and destroys a useful index, making things worse. Answer B misunderstands the issue — adding another index on the same text column doesn't help if the conversion still wraps that column; the index still can't be used for a seek. Answer D is a common misconception: moving the conversion to the SELECT list sounds clever, but the join comparison itself remains type-mismatched, so the plan doesn't change at all. As a study tip, always ask yourself: Is a function or conversion applied to an indexed column in the WHERE or JOIN clause? If yes, the index is likely bypassed — and the fix is always to eliminate the mismatch at the data level, not to shuffle the conversion elsewhere.