Indexing Strategy and Selection Questions

Covers index design principles and practical selection of indexes to accelerate queries while managing storage and write cost. Topics include index types such as B tree hash and bitmap indexes and full text and functional indexes; single column composite and covering indexes; clustered versus nonclustered index architectures and partial or filtered indexes. Candidates should reason about index selectivity and cardinality and how statistics and histograms influence optimizer choices. Also assess index maintenance overhead fragmentation and rebuild strategies and the trade off between faster reads and slower inserts updates and deletes. Practical skills include reading execution plans to identify missing or inefficient indexes proposing index consolidation or covering index designs testing and benchmarking index changes and understanding interactions between indexing partitioning and denormalization.

MediumTechnical

0 practiced

Given these two tables:

orders(order_id PK, customer_id, order_date, total_amount)customers(customer_id PK, country, tier)

Query: SELECT o.order_id, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.country = 'US' AND o.order_date >= '2025-01-01' ORDER BY o.total_amount DESC LIMIT 50;

Propose one or more indexes (SQL statements) to improve that query and explain your reasoning about column order and covering possibilities.

EasyTechnical

0 practiced

List the common index types a BI analyst should know (B-tree, hash, bitmap, full-text, functional/expression) and provide a one-sentence description plus a typical BI use-case for each (e.g., date range filters, equality joins, low-cardinality filters, product-descriptions search, case-insensitive joins).

MediumTechnical

0 practiced

Two candidate composite indexes exist: (a,b,c) and (b,a,c). Given a query workload with predicates frequently on a and sometimes on both a and b, explain which ordering you would choose and why. Discuss how prefix usage and selectivity drive the order.

HardTechnical

0 practiced

Explain how index fragmentation occurs in row-store databases and propose an incremental rebuild strategy for very large indexes in SQL Server or PostgreSQL that minimizes downtime. Discuss online vs offline rebuilds and the trade-offs of each.

HardTechnical

0 practiced

Create an index audit plan for a reporting schema that contains 500 tables and 1,200 indexes. Which automated checks would you implement to find unused, duplicate, or highly-costly indexes, how would you prioritize candidates for removal, and what safety checks would you include before dropping indexes in production?

Unlock Full Question Bank

Get access to hundreds of Indexing Strategy and Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.