Indexing Interview Questions

February 26, 2026 • By Surya Singh • Database • Indexing • SQL • Interview

Database indexing interview questions — B-tree, composite indexes, query optimization.

DatabaseIndexingSQLInterview

Key Takeaways

  • 1Indexes speed up queries by creating a sorted structure (B-tree, hash) for fast lookups.
  • 2Index what you filter, sort, or join on; avoid over-indexing (slows writes, uses space).
  • 3Composite indexes: column order matters—leftmost prefix rule.
  • 4Covering index: index includes all columns needed by the query, avoiding table lookup.

The questions below are commonly asked in technical interviews. Each answer is written to help you understand the concept clearly and explain it confidently. Focus on understanding the "why" behind each answer—that is what interviewers care about.

In this guide

Interview Questions & Answers

What is a database index and how does it work?

An index is a data structure (usually a B-tree) that stores a subset of columns in sorted order, along with pointers to the full row. Instead of scanning the entire table (full table scan), the database can use the index to find rows quickly—O(log n) lookups for B-trees. For a query like "SELECT * FROM users WHERE email = ?", an index on email lets the database jump directly to matching rows. Indexes trade write speed and storage for read speed: every insert/update/delete must update the index.

// Raw SQL - same DDL in C# (EF migration or DbCommand)
// CREATE INDEX idx_users_email ON users(email);
// Composite: CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
// Covering: CREATE INDEX idx_users_status_name_email 
//          ON users(status) INCLUDE (name, email);

// Entity Framework Core migration
migrationBuilder.CreateIndex(
    name: "idx_users_email",
    table: "users",
    column: "email");

When should I create an index?

Create indexes on columns you frequently filter (WHERE), sort (ORDER BY), or join on. One index per query pattern is a starting point. Avoid indexing every column—each index slows writes and uses space. Low-cardinality columns (e.g., boolean, enum with few values) often do not benefit much. High-cardinality columns (email, id, created_at) are good candidates. Monitor slow query logs and add indexes for queries that appear there. Remove unused indexes.

What is a composite index and how does column order matter?

A composite index includes multiple columns (e.g., INDEX(a, b, c)). The columns are ordered: the index is sorted by a, then by b within same a, then by c within same a and b. The leftmost prefix rule: a query can use the index if it filters on a, or (a, b), or (a, b, c)—but not (b, c) alone, because the index is not sorted by b first without a. So put the most selective or frequently filtered column first. For "WHERE a=? AND b=? ORDER BY c", index (a, b, c) works well.

What is a covering index?

A covering index includes all columns needed for a query, so the database does not need to look up the full row (index-only scan). For "SELECT name, email FROM users WHERE status = 'active'", an index on (status, name, email) is covering—the query can be satisfied entirely from the index. This avoids random I/O to fetch rows and can significantly speed up queries. The trade-off: the index is larger and writes are costlier. Use for hot queries.

What causes index fragmentation and how do I fix it?

Fragmentation occurs when rows are inserted, updated, or deleted—the index structure becomes less compact, pages have free space, and logical order does not match physical order. This can slow scans. Fix: run REINDEX (PostgreSQL) or OPTIMIZE TABLE (MySQL) to rebuild the index. Do this during low-traffic periods—it can lock the table. In PostgreSQL, VACUUM helps with dead tuples; REINDEX rebuilds the index. Monitor index bloat and schedule maintenance.

Loading...

XLinkedInFacebook
Surya Singh

Surya Singh

Azure Solutions Architect & AI Engineer

Microsoft-certified Azure Solutions Architect with 8+ years in enterprise software, cloud architecture, and AI/ML deployment. I build production AI systems and write about what actually works—based on shipping code, not theory.

  • Microsoft Certified: Azure Solutions Architect Expert
  • Built 20+ production AI/ML pipelines on Azure
  • 8+ years in .NET, C#, and cloud-native architecture
LinkedInTwitter/XAbout Me