Database indexing in HLD - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define the main purpose of a database index.

HLD

A database index is used to [1] data retrieval speed.

Drag options to blanks, or click blank then click option'

Aignore

Bduplicate

Cincrease

Ddecrease

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing index with data duplication.

Thinking index slows down retrieval.

The main purpose of a database index is to increase data retrieval speed by allowing faster searches.

2fill in blank

medium

Complete the code to specify the common data structure used for database indexes.

HLD

Most database indexes use a [1] tree structure for efficient searching.

Drag options to blanks, or click blank then click option'

Ahash

Bbinary

Clinked

DB+

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing binary tree which is not balanced for databases.

Confusing hash with tree structures.

B+ trees are commonly used in databases because they keep data sorted and allow fast insertion, deletion, and search.

3fill in blank

hard

Fix the error in the statement about index types.

HLD

A [1] index stores a mapping from keys to row locations using a hash function.

Drag options to blanks, or click blank then click option'

Ahash

Bbitmap

Cclustered

Dfull-text

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing clustered index with hash index.

Choosing bitmap index which uses bitmaps, not hash functions.

Hash indexes use a hash function to map keys to row locations, enabling fast equality searches.

4fill in blank

hard

Fill both blanks to complete the description of clustered indexes.

HLD

A clustered index [1] the data rows in the table based on the index key, unlike a non-clustered index which [2] a separate structure.

Drag options to blanks, or click blank then click option'

Asorts

Bstores

Cmaintains

Dcreates

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up 'stores' and 'sorts' for clustered index.

Thinking non-clustered index sorts data rows.

Clustered indexes sort the actual data rows according to the index key. Non-clustered indexes maintain a separate structure pointing to data.

5fill in blank

hard

Fill all three blanks to complete the code for a simple index creation statement.

HLD

CREATE [1] INDEX [2] ON [3] (column_name);

Drag options to blanks, or click blank then click option'

AUNIQUE

Bidx_customer_name

Ccustomers

DTEMPORARY

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE when temporary index is intended.

Mixing table and index names.

This statement creates a temporary index named 'idx_customer_name' on the 'customers' table.

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