Questions: Index Design and Selection Strategy

Question 1

A table has a composite index on (country, city). A query filters only on city. How will the database handle this?

• A. It will use the composite index efficiently, since city is one of the indexed columns
• B. It will use only the city portion of the composite index, skipping the country portion
• C. It will likely not use the composite index, since the leftmost prefix (country) is not in the query
• D. It will use the index but only if the city column has high selectivity

Answer: (C)

B-tree composite indexes are ordered left to right: (country, city) sorts first by country, then by city within each country. A filter on city alone cannot navigate this structure efficiently — the database would need to examine every country's entries. This leftmost prefix rule means the composite index on (country, city) is effectively useless for city-only queries. To support city-only queries, a separate index on city is needed. Column order in a composite index is one of the most important and commonly misunderstood aspects of index design.

Question 2

A table has 1 million rows and a 'status' column with three possible values: 'active' (60%), 'inactive' (35%), 'suspended' (5%). A developer adds an index on status to speed up queries filtering by status. What is the most likely outcome?

• A. The index will dramatically speed up all queries filtering by status
• B. The index will be most useful for status = 'suspended' queries but may not help for 'active' or 'inactive'
• C. The index will slow down reads because the database must scan the entire index before the table
• D. The index will have no effect because it was added after the table was populated

Answer: (B)

Selectivity determines whether an index helps. 'active' and 'inactive' each return hundreds of thousands of rows — a third or more of the table — at which point a full table scan can be faster than following index pointers to scattered row locations. But 'suspended' (5%) returns 50,000 rows, a narrow result where the index meaningfully reduces work. Low-selectivity columns make poor index candidates for common values. This is why the general rule is to prioritize high-selectivity columns — those with many distinct values relative to row count.

Question 3

Adding more indexes to a frequently-written table can improve overall database performance by caching more data in memory.

• T. True
• F. False

Answer: False

More indexes slow writes, not improve them. Every INSERT, UPDATE, or DELETE must update all indexes on the affected columns — the more indexes a table has, the more work each write operation requires. Indexes also consume disk space and memory buffer pool capacity. Over-indexing is a real problem in production systems: tables with ten or twenty indexes can have dramatically slower write throughput. The right number of indexes balances read performance gains against write overhead and storage costs, guided by the actual query workload.

Question 4

A covering index can answer a query entirely from the index structure without accessing the underlying table rows.

• T. True
• F. False

Answer: True

A covering index is one that contains all the columns a query needs — the SELECT columns, WHERE filters, and JOIN keys. When the index covers the query, the database can return results directly from the index without a 'table lookup' (also called a heap fetch or bookmark lookup). This is a major performance win because index pages are much smaller than table pages, so the same data fits in far fewer I/O operations. Identifying opportunities for covering indexes is one of the highest-leverage query optimization techniques.

Question 5

Explain the leftmost prefix rule for composite indexes and why column order in a composite index matters enormously.

Model answer: A composite index on columns (A, B, C) is stored sorted first by A, then by B within each A value, then by C within each (A, B) pair. The database can only use the index efficiently for queries that filter on a prefix of this left-to-right order: A alone, A and B together, or A, B, and C together. Queries filtering only on B or C cannot navigate the index structure, because the values of B are interleaved across all A values with no useful global ordering. Column order should place the most selective equality-condition columns first, followed by range condition columns, to maximize the proportion of the index that narrows the search.

The leftmost prefix rule has a practical design implication: a single composite index on (user_id, created_at) can serve both 'show all records for user X' queries and 'show all records for user X after date Y' queries, but not 'show all records after date Y' queries without knowing the user. Designing composite indexes requires analyzing the query workload to identify which column combinations appear together most often, then ordering those columns to support as many query patterns as possible with a single index.