Database indexing in HLD - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the purpose of database indexes

Which of the following best explains why database indexes are used?

A To encrypt data for security purposes

B To increase the size of the database for better storage

C To speed up data retrieval by reducing the amount of data scanned

D To backup the database automatically

💡 Hint

Think about how indexes help find data faster, like an index in a book.

Indexes help the database find rows faster by avoiding scanning the entire table.

❓ Architecture

intermediate

Choosing the right index type for a query

You have a table with millions of rows and a query that filters by a range of dates. Which index type is most suitable?

A Full-text index

B Hash index

C Bitmap index

D B-tree index

💡 Hint

Consider which index supports range queries efficiently.

B-tree indexes are designed to handle range queries efficiently by maintaining sorted order.

❓ scaling

advanced

Scaling read performance with indexes

Your application experiences slow read queries on a large table. You add multiple indexes. What is a potential downside of adding many indexes?

A Reduced read performance because indexes slow down queries

B Increased write latency due to index maintenance

C Indexes consume less disk space, causing storage issues

D Indexes automatically delete old data, causing data loss

💡 Hint

Think about what happens when data is inserted or updated in indexed tables.

Each index must be updated on writes, which can slow down insert, update, and delete operations.

❓ tradeoff

advanced

Tradeoffs between clustered and non-clustered indexes

Which statement correctly describes a key difference between clustered and non-clustered indexes?

A Clustered indexes store data rows in the index order; non-clustered indexes store pointers to data rows

B Non-clustered indexes store data rows in the index order; clustered indexes store pointers to data rows

C Non-clustered indexes always use less disk space than clustered indexes

D Clustered indexes cannot be created on primary keys; non-clustered indexes can

💡 Hint

Consider how data is physically stored in clustered indexes.

Clustered indexes define the physical order of data rows; non-clustered indexes contain references to data rows.

❓ estimation

expert

Estimating index size impact on storage

You have a table with 100 million rows and a single-column B-tree index on a 4-byte integer column. Approximately how much disk space will the index consume if each index entry requires 20 bytes?

A About 2 GB

B About 200 MB

C About 20 GB

D About 200 GB

💡 Hint

Multiply the number of rows by the size per index entry and convert bytes to gigabytes.

100 million rows × 20 bytes = 2,000,000,000 bytes ≈ 2 GB.