Database indexing best practices in relational systems: Which “rule of thumb” should guide when you choose columns to index for query performance and maintainability?

Introduction / Context:
Choosing the right columns for indexing is essential in relational database design. The goal is to speed up selective lookups and joins while controlling write overhead and storage. This question tests pragmatic “rules of thumb,” especially around columns that can contain nulls.

Given Data / Assumptions:

• Workload includes OLTP-style queries with WHERE predicates and joins.
• Some columns may contain null values.
• We must select the best general guideline among the options.

Concept / Approach:

Indexes accelerate reads by ordering keys and maintaining pointers, but they add overhead on writes. Columns with many nulls can behave unexpectedly: some engines treat nulls specially in unique indexes; null-heavy columns may have low selectivity; partial or filtered indexes may be preferable. Therefore a sensible rule of thumb is to be cautious when indexing nullable attributes and consider patterns like filtered indexes (WHERE col IS NOT NULL) or enforcing NOT NULL when appropriate.

Step-by-Step Solution:

Verification / Alternative check:

Inspect query plans for predicates involving nullable columns; compare performance with and without a filtered index that excludes nulls.

Why Other Options Are Wrong:

Indexes more useful on smaller tables: smaller tables are often fully scanned cheaply.

Indexes on columns not used in WHERE: provide little benefit.

No unique index for primary key: primary keys must be unique; engines auto-create or require a unique index.

Common Pitfalls:

Indexing low-selectivity columns (e.g., boolean flags) or leaving NULLs rampant in keys used for joins.

Final Answer:

Be careful indexing attributes that have null values.