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.

Practice

1. What is the primary purpose of a database index?

easy

A. To increase the size of the database

B. To speed up data retrieval by allowing quick lookups

C. To store backup copies of data

D. To encrypt the database contents

Solution

1. Step 1: Understand what an index does

   An index helps the database find data faster by creating a quick lookup structure.

2. Step 2: Identify the main benefit

   This quick lookup reduces the time needed to search large tables.

3. Final Answer:

   To speed up data retrieval by allowing quick lookups -> Option B

4. Quick Check:

   Index = Faster data search [OK]

Hint: Indexes speed up searches by creating shortcuts [OK]

Common Mistakes:

• Thinking indexes store extra data copies
• Confusing indexes with backups
• Assuming indexes encrypt data

2. Which of the following is the correct syntax to create an index on the column user_id in SQL?

easy

A. CREATE INDEX idx_user ON users (user_id);

B. INDEX CREATE user_id ON users;

C. CREATE users INDEX ON user_id;

D. MAKE INDEX user_id IN users;

Solution

1. Step 1: Recall SQL syntax for creating indexes

   The standard syntax is CREATE INDEX index_name ON table_name (column_name);

2. Step 2: Match the syntax with options

   CREATE INDEX idx_user ON users (user_id); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_user ON users (user_id); -> Option A

4. Quick Check:

   CREATE INDEX ... ON ... (column) [OK]

Hint: Remember: CREATE INDEX name ON table(column) [OK]

Common Mistakes:

• Swapping table and column names
• Using wrong keyword order
• Missing parentheses around column

3. Given a table orders with an index on order_date, what will be the output of the query plan for SELECT * FROM orders WHERE order_date = '2023-01-01';?

medium

A. Full table scan without using the index

B. Ignoring the order_date column completely

C. Sorting all rows by order_date before filtering

D. Index seek on order_date to quickly find matching rows

Solution

1. Step 1: Understand how indexes affect query plans

   An index on order_date allows the database to quickly locate rows matching the date.

2. Step 2: Identify the expected query plan

   The database will perform an index seek to find matching rows efficiently, not scan the whole table.

3. Final Answer:

   Index seek on order_date to quickly find matching rows -> Option D

4. Quick Check:

   Indexed column = index seek [OK]

Hint: Indexed WHERE columns use index seek, not full scan [OK]

Common Mistakes:

• Assuming full table scan despite index
• Confusing sorting with filtering
• Ignoring index usage in query plan

4. A developer created an index on a column but notices no improvement in query speed. What could be a likely reason?

medium

A. The database automatically disables all indexes

B. The index was created with the wrong SQL syntax

C. The queries do not filter or sort using the indexed column

D. Indexes always slow down queries

Solution

1. Step 1: Analyze index usage conditions

   Indexes only help if queries use the indexed column in WHERE, JOIN, or ORDER BY clauses.

2. Step 2: Identify why no speed improvement occurs

   If queries don't use the indexed column, the index is ignored, so no speed gain.

3. Final Answer:

   The queries do not filter or sort using the indexed column -> Option C

4. Quick Check:

   Index helps only if column used in query [OK]

Hint: Index unused if queries ignore indexed columns [OK]

Common Mistakes:

• Assuming syntax error disables index
• Believing all indexes slow queries
• Thinking database disables indexes by default

5. You have a large table with frequent inserts and queries filtering by status and created_at. Which indexing strategy balances read speed and write overhead best?

hard

A. Create a composite index on (status, created_at)

B. Create separate indexes on status and created_at individually

C. Create no indexes to avoid write overhead

D. Create a full-text index on status and created_at

Solution

1. Step 1: Understand composite vs separate indexes

   A composite index on (status, created_at) helps queries filtering on both columns efficiently.

2. Step 2: Consider write overhead and query patterns

   Separate indexes increase write overhead more than one composite index and may be less efficient for combined filters.

3. Step 3: Exclude unsuitable options

   No indexes hurt read speed; full-text index is for text search, not filtering by status/date.

4. Final Answer:

   Create a composite index on (status, created_at) -> Option A

5. Quick Check:

   Composite index = balanced read/write [OK]

Hint: Composite indexes optimize multi-column filters with less overhead [OK]

Common Mistakes:

• Creating many separate indexes causing slow writes
• Skipping indexes hurting read performance
• Using full-text index for non-text filtering