Index selection guidelines in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

When to use an index on a database column?

Which situation is the best reason to create an index on a database column?

A The column stores large text data that is rarely searched.

B The column is updated very frequently but never used in queries.

C The column is frequently used in WHERE clauses to filter rows.

D The column contains unique values but is never queried.

💡 Hint

Think about when indexes help speed up data retrieval.

Indexes improve query speed when columns are used to filter or join data. Columns rarely searched or frequently updated may not benefit from indexes.

❓ query_result

intermediate

Effect of index on query execution

Given a table Employees with an index on DepartmentID, what is the expected output of this query's execution plan?

DBMS Theory

EXPLAIN SELECT * FROM Employees WHERE DepartmentID = 5;

A The query sorts all rows by DepartmentID before filtering.

B The query performs a full table scan ignoring the index.

C The query returns an error because indexes cannot be used in WHERE clauses.

D The query uses the index to quickly find rows with DepartmentID = 5.

💡 Hint

Indexes help speed up searches on indexed columns.

When a column has an index, the database can use it to quickly locate matching rows instead of scanning the whole table.

📋 Factual

advanced

Correct syntax to create a composite index

Which SQL statement correctly creates a composite index on columns LastName and FirstName in the Customers table?

A CREATE INDEX idx_name ON Customers (LastName FirstName);

B CREATE INDEX idx_name ON Customers (LastName, FirstName);

C CREATE INDEX idx_name ON Customers LastName, FirstName;

D CREATE INDEX idx_name (LastName, FirstName) ON Customers;

💡 Hint

Check the order of keywords and parentheses in CREATE INDEX syntax.

The correct syntax places the index name after CREATE INDEX, then ON table name, then the columns in parentheses separated by commas.

❓ optimization

advanced

Choosing the best index for query optimization

You have a query filtering on City and Age columns. Which index choice will most likely improve performance best?

A A composite index on (City, Age).

B Separate indexes on City and Age columns.

C No index, rely on full table scan.

D An index only on Age column.

💡 Hint

Think about how composite indexes help with multiple column filters.

A composite index on both columns helps the database quickly filter rows matching both conditions in one index lookup.

🔍 Analysis

expert

Why does this index not improve query speed?

A table has an index on LastName. The query is:
SELECT * FROM Users WHERE UPPER(LastName) = 'SMITH';
Why might the index not be used?

A Because the query applies a function (UPPER) on the indexed column, preventing index use.

B Because the index is only for numeric columns, not text.

C Because the query is missing an ORDER BY clause.

D Because the table has too few rows to use indexes.

💡 Hint

Consider how functions on columns affect index usage.

Applying a function on an indexed column usually disables the use of that index unless a function-based index exists.

Practice

1. Which of the following is the best reason to create an index on a database column?

easy

A. To make data entry faster

B. To reduce the size of the database

C. To speed up searches on that column

D. To prevent data duplication

Solution

1. Step 1: Understand the purpose of an index

   An index is like a shortcut that helps the database find rows faster when searching by that column.

2. Step 2: Compare options with index purpose

   Only speeding up searches matches the main use of indexes; other options do not relate to indexing benefits.

3. Final Answer:

   To speed up searches on that column -> Option C

4. Quick Check:

   Indexes improve search speed = A [OK]

Hint: Indexes speed up searches, not data entry or size [OK]

Common Mistakes:

• Thinking indexes reduce database size
• Believing indexes speed up data insertion
• Confusing indexes with uniqueness constraints

2. Which of the following is the correct SQL syntax to create an index named idx_name on the column last_name of the table employees?

easy

A. CREATE INDEX idx_name ON employees (last_name);

B. CREATE idx_name INDEX ON employees (last_name);

C. INDEX CREATE idx_name ON employees (last_name);

D. CREATE INDEX ON employees idx_name (last_name);

Solution

1. Step 1: Recall standard SQL syntax for creating an index

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

2. Step 2: Match options to syntax

   CREATE INDEX idx_name ON employees (last_name); matches the correct syntax exactly; others have wrong order or keywords.

3. Final Answer:

   CREATE INDEX idx_name ON employees (last_name); -> Option A

4. Quick Check:

   Standard SQL index creation = C [OK]

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

Common Mistakes:

• Swapping keywords order
• Omitting the INDEX keyword
• Placing index name after table name incorrectly

3. Consider a table orders with columns order_id, customer_id, and order_date. If you create an index on customer_id, what will be the expected effect when running this query?
SELECT * FROM orders WHERE customer_id = 123;

medium

A. The query will run slower because indexes slow down searches

B. The query will cause an error due to the index

C. The query will return no results because indexes filter data

D. The query will run faster because the index helps find matching rows quickly

Solution

1. Step 1: Understand index effect on search queries

   An index on customer_id allows the database to quickly locate rows where customer_id = 123 without scanning the whole table.

2. Step 2: Analyze query behavior with index

   The query uses a WHERE condition on customer_id, so the index speeds up the search, making the query faster.

3. Final Answer:

   The query will run faster because the index helps find matching rows quickly -> Option D

4. Quick Check:

   Index speeds up WHERE searches = B [OK]

Hint: Indexes speed up WHERE filters on indexed columns [OK]

Common Mistakes:

• Thinking indexes slow down searches
• Believing indexes filter out data
• Assuming indexes cause errors in queries

4. You created an index on the email column of the users table, but after inserting many new users, the database performance for inserts slowed down significantly. What is the most likely cause?

medium

A. The index was created on the wrong column

B. Indexes slow down data insertion because they must update with each insert

C. The database does not support indexes on email columns

D. The table is too small for indexes to help

Solution

1. Step 1: Understand index impact on data changes

   Indexes improve search speed but add overhead during inserts because the index structure must be updated for each new row.

2. Step 2: Analyze why inserts slow down

   Since the index updates on every insert, many inserts cause slower performance, which matches Indexes slow down data insertion because they must update with each insert.

3. Final Answer:

   Indexes slow down data insertion because they must update with each insert -> Option B

4. Quick Check:

   Indexes slow inserts due to update overhead = A [OK]

Hint: Indexes slow inserts due to update work [OK]

Common Mistakes:

• Blaming wrong column choice for insert slowdown
• Thinking indexes cause errors on email columns
• Assuming small tables don't need indexes

5. You have a large sales table with columns sale_id, product_id, sale_date, and region. You often run queries filtering by product_id and region together. Which index strategy is best to improve query speed without hurting insert performance too much?

hard

A. Create a composite index on (product_id, region)

B. Create separate indexes on product_id and region

C. Create an index only on sale_date

D. Do not create any indexes to keep inserts fast

Solution

1. Step 1: Analyze query filter columns

   Queries filter by both product_id and region together, so a composite index on both columns helps the database find matching rows efficiently.

2. Step 2: Compare index strategies

   Separate indexes may help but are less efficient for combined filters; indexing sale_date is irrelevant here; no index hurts query speed.

3. Final Answer:

   Create a composite index on (product_id, region) -> Option A

4. Quick Check:

   Composite index matches multi-column filters = D [OK]

Hint: Use composite index for multi-column filters [OK]

Common Mistakes:

• Creating separate indexes instead of composite
• Indexing unrelated columns
• Avoiding indexes and hurting query speed