Indexing Strategies (B-Tree, Hash, Full-Text) and Query Optimization: 20 Real Interview Questions

Database indexing fundamentals are like phone books for databases. Without an index, finding a customer by email means scanning every single row—slow! With an index, the database jumps directly to matching rows. Indexing strategy separates slow systems from fast ones, and understanding B-Trees, Hash indexes, and Full-Text search is essential for senior engineers.

Understanding Database Indexing Fundamentals

Think of indexing like a library card catalog. The catalog (index) is sorted alphabetically by author, pointing to shelf locations. Without it, youd search every book manually. Database indexes work the same way: they sort column values and store pointers to actual rows. Queries use indexes to jump directly to matching data instead of scanning all rows.

Indexing fundamentals answer a core question: trade-off. Indexes speed up reads but slow down writes (because the index must be updated). Too many indexes waste storage; too few cause slow queries. The art is choosing which columns to index.

B-Tree Indexes: The Most Common Type

B-Tree (Balanced Tree) indexes are used by most databases. Imagine a tree structure: the root branches into multiple paths, each leading deeper until reaching leaf nodes (actual data). The advantage: B-Trees are balanced, so searching always takes the same time. No branch is longer than others. Most databases use B-Tree by default because they work well for range queries: WHERE age BETWEEN 25 AND 35 quickly jumps to the 25 leaf node and scans through 35.

In layman terms: B-Trees are like a well-organized filing cabinet. Each drawer (branch) is subdivided into folders (leaves). Finding something takes consistent effort because the structure is balanced.

Hash Indexes: Fast for Exact Matches

Hash indexes use a mathematical function to map values to locations. WHERE customer_id = 5 hashes 5 and jumps directly to that location in constant time (O(1)). No tree traversal needed. However, hash indexes fail for range queries: WHERE customer_id > 5 cant use a hash index efficiently because hashing doesnt preserve order.

In layman terms: Hash indexes are like a jukebox. You input a song title (key), and it jumps directly to the songs location. But if you want songs from artist A to Z, the jukebox cant help—it only knows individual song locations, not order.

Full-Text Indexes: Searching Text Content

Full-Text indexes search inside text columns (article body, product description). Regular indexes find exact matches; full-text indexes find words within text. MATCH(description) AGAINST(laptop computer IN BOOLEAN MODE) searches for “laptop” OR “computer” in descriptions. Full-Text indexes tokenize text (split into words), remove stop words (“the”, “a”), and index remaining words for fast searching.

In layman terms: Full-Text indexes are like a book index. The book index lists important keywords from each chapter and page numbers. To find “database optimization”, you look it up in the index instead of reading the entire book.

Query Optimization: Using Indexes Effectively

Indexing fundamentals teach that indexes dont help all queries. EXPLAIN shows how the database executes a query: using an index (efficient) or table scan (slow). Poor index selection, missing columns in WHERE clause, or queries that cant use indexes require optimization. Covered indexes (all SELECT columns in the index) avoid table lookups entirely.

Indexing strategy fundamentals: index columns in WHERE clauses, especially heavily filtered columns. Index columns in JOIN ON conditions. Avoid indexing columns with few unique values (gender, boolean flags).

5 Complete Interview Questions with Solutions

Question 1: When to Create an Index

A query searches for customers by email: WHERE email = user@example.com. Should you index the email column? What queries benefit from this index?

-- Slow without index (table scan)
SELECT * FROM customers WHERE email = user@example.com;
-- Scans all 1M customers

-- Fast with index
CREATE INDEX idx_email ON customers(email);
-- Now jumps directly to matching row

-- Index also helps:
WHERE email LIKE user% -- Range scan
WHERE email IN (a@ex.com, b@ex.com) -- Multiple lookups

-- Index doesnt help:
WHERE LOWER(email) = LOWER(search) -- Function applied
WHERE email LIKE %example.com -- Wildcard at start

Explanation: Index email because its heavily filtered. B-Tree indexes support exact matches, range queries, and LIKE with leading characters. Indexing fundamentals: functions on indexed columns disable the index. Email is unique (should be UNIQUE constraint), making the index even more valuable for lookups.

Google – Senior Database EngineerAmazon – Senior DBAMicrosoft – Data Architect

Question 2: B-Tree vs Hash Indexes

For a product table with frequent range queries (WHERE price BETWEEN 10 AND 100), should you use a B-Tree or Hash index on the price column?

-- B-Tree (correct for ranges)
CREATE INDEX idx_price ON products(price) USING BTREE;
WHERE price BETWEEN 10 AND 100 -- Efficient: finds 10, scans to 100

-- Hash (wrong for ranges)
CREATE INDEX idx_price ON products(price) USING HASH;
WHERE price BETWEEN 10 AND 100 -- Inefficient: must scan all rows

-- Why: B-Tree preserves order (10→20→30→...→100)
-- Hash scatters values: 10→location1, 20→location5, 30→location2
-- No sequential order, so range queries fail

Explanation: B-Tree for range queries, Hash for equality. Indexing fundamentals: understand the query pattern. Price BETWEEN uses ranges; Hash indexes lose order during hashing. Most databases default to B-Tree because it handles both exact and range queries.

Meta – Senior Database EngineerOracle – Data ArchitectIBM – Senior DBA

Question 3: Full-Text Search vs LIKE

Searching product descriptions: LIKE %laptop% vs MATCH(description) AGAINST(laptop). Which is faster on 1M products?

-- LIKE (slow on large text)
WHERE description LIKE %laptop%
-- Wildcard at start = table scan, no index
-- Checks every row for laptop substring
-- 1M products = slow

-- Full-Text (fast)
CREATE FULLTEXT INDEX idx_desc ON products(description);
WHERE MATCH(description) AGAINST(laptop IN BOOLEAN MODE)
-- Tokenizes description: [powerful, laptop, computer]
-- Finds laptop in inverted index instantly
-- 1M products = fast

-- Full-Text also supports:
AGAINST(laptop OR computer) -- OR search
AGAINST(+laptop -cheap) -- Must have laptop, NOT cheap

Explanation: Full-Text is designed for searching text content. LIKE with leading wildcards (%text) cant use regular indexes. Full-Text indexes tokenize and store word positions, enabling instant word searches. Indexing fundamentals: match the index type to the query pattern.

Stripe – Senior Data EngineerLinkedIn – Database Architect

Question 4: Covered Indexes

A query: SELECT name, email FROM customers WHERE status = active. How does a covered index make this query faster?

-- Regular index (two lookups)
CREATE INDEX idx_status ON customers(status);
-- Step 1: Use index to find rows WHERE status = active
-- Step 2: Look up actual rows to get name, email

-- Covered index (one lookup)
CREATE INDEX idx_status_covered ON customers(status, name, email);
-- Step 1: Use index to find status = active
-- Step 2: Get name, email from index itself (no table lookup)

-- Covered index stores: [status, name, email] together
-- Query uses only these columns, never touches table
-- Called "Index-only scan" = fastest

Explanation: Covered indexes include all SELECT columns in the index. The database never touches the main table—all data is in the index. Indexing fundamentals: covered indexes are powerful but use extra storage. Use for frequently accessed queries.

Google – Senior Data ArchitectAmazon – Database Engineer

Question 5: Query Optimization with EXPLAIN

EXPLAIN shows “Full Table Scan” for a query that should use an index. What went wrong, and how do you fix it?

-- Problem 1: Function on indexed column
EXPLAIN SELECT * FROM customers WHERE YEAR(created_date) = 2024;
-- Result: Full Table Scan (function disables index)

-- Fix: Move function outside WHERE
SELECT * FROM customers 
WHERE created_date >= 2024-01-01 
  AND created_date < 2025-01-01;
-- Now uses index on created_date

-- Problem 2: Data type mismatch
EXPLAIN SELECT * FROM users WHERE user_id = 123; -- user_id is INT
-- Result: Full Table Scan (string 123 != int 123)

-- Fix: Use correct type
SELECT * FROM users WHERE user_id = 123;
-- Now uses index

-- Problem 3: Missing index
-- Solution: CREATE INDEX idx_name ON table(column);

Explanation: EXPLAIN reveals why queries are slow. Common problems: functions disabling indexes, type mismatches, missing indexes. Indexing fundamentals: use EXPLAIN to verify query plans. Cost-based optimizers choose indexes automatically, but you must enable them by writing queries correctly.

Microsoft – Senior Database ArchitectMeta – Data Engineer

15 Practice Questions (Test Yourself)

Question 6: When you have a table with millions of rows and create five different single-column indexes to cover various WHERE clauses, how would excessive indexing impact write performance, and when should you consolidate indexes into composite indexes?

Google – Senior Data EngineerAmazon – Senior DBA

Question 7: If youre building a search feature for product names that need exact matches and prefix searches like Sony% but not substring searches like %phone%, how would you index and optimize these different query patterns?

Microsoft – Senior Database EngineerMeta – Data Architect

Question 8: How would using a Hash index on columns with only a few unique values like gender or employment_status affect query performance compared to no index at all?

Oracle – Senior Data ArchitectIBM – Database EngineerLinkedIn – Senior DBA

Question 9: When using Full-Text indexes to search multiple language documents in a database, what challenges arise with stop words and stemming, and how would you configure Full-Text search to handle multiple languages correctly?

Stripe – Senior Data EngineerGoogle – Database Architect

Question 10: If a critical query involves both equality conditions and range conditions like WHERE status = active AND created_date BETWEEN x AND y, how would you design a composite index column order to maximize efficiency?

Amazon – Senior Data ArchitectMicrosoft – Database EngineerMeta – Senior DBA

Question 11: When an EXPLAIN plan shows that the database optimizer chose a Full Table Scan instead of using an available index, what diagnostic steps would you take to understand whether the optimizer made the right choice or if you should force index usage?

Flipkart – Senior Database EngineerLinkedIn – Data Architect

Question 12: How would you handle index maintenance tasks like rebuilding fragmented indexes and updating statistics on a production system with thousands of concurrent queries without causing locking issues?

Google – Senior Data ArchitectOracle – Senior DBAIBM – Data Architect

Question 13: If youre migrating from a relational database to a NoSQL system and need to adapt your indexing strategy for document-based querying, how would traditional B-Tree indexing concepts translate to document indexes?

Stripe – Database EngineerAmazon – Senior Data Engineer

Question 14: When designing indexes for a data warehouse with massive analytical queries joining multiple large tables, would traditional indexing strategies still apply, or would you need completely different optimization approaches?

Microsoft – Senior Data ArchitectMeta – Database EngineerGoogle – BI Architect

Question 15: If a query combines indexed and non-indexed columns in a WHERE clause like WHERE indexed_col = 1 AND non_indexed_col = value, how would the database execute this query, and would you need to add another index?

LinkedIn – Senior Data EngineerFlipkart – Data Architect

Question 16: How would you handle the trade-off between creating a covering index that includes many columns versus creating separate indexes, considering both query speed and write performance impact?

Stripe – Senior Data ArchitectAmazon – Database EngineerOracle – Senior DBA

Question 17: When implementing Full-Text search for e-commerce products with user-generated content that may contain typos and misspellings, how would you extend Full-Text indexes to support fuzzy matching or phonetic search?

Google – Senior Data EngineerIBM – Data Architect

Question 18: If you need to optimize for both point lookups (WHERE id = 1) and range scans (WHERE created_date BETWEEN x AND y) on the same table, what indexing approach would you recommend to satisfy both query patterns?

Meta – Senior Data ArchitectMicrosoft – Database EngineerLinkedIn – Senior BI Engineer

Question 19: When a slow query involves filtering on a low-cardinality column like status with only three unique values, would creating an index on that column provide sufficient performance improvement, or would you need a different optimization strategy?

Stripe – Database EngineerAmazon – Senior Data Architect

Question 20: How would you approach index design and query optimization for a system that requires sub-second response times on massive datasets, and what monitoring and tuning strategies would you implement to maintain performance over time?

Google – Senior Database ArchitectOracle – Senior DBAIBM – Data Architect

Ready to Master Indexing & Query Optimization?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.