Index selection guidelines in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main purpose of creating an index in a database?

An index helps speed up data retrieval by allowing the database to find rows faster without scanning the entire table.

beginner

When should you avoid creating an index on a database column?

Avoid indexing columns that have many duplicate values or are updated very frequently, as indexes can slow down write operations.

beginner

Why is it recommended to create indexes on columns used in WHERE clauses?

Because indexes on columns used in WHERE clauses help the database quickly filter rows matching the condition, improving query speed.

intermediate

What type of columns are best suited for indexing?

Columns with high selectivity, meaning they have many unique values, are best for indexing because they help narrow down search results effectively.

intermediate

How does a composite index differ from a single-column index?

A composite index includes multiple columns and is useful when queries filter or sort by more than one column together.

Which column is best to index for faster searches?

A A column that is rarely used in queries

B A column with mostly repeated values

C A column with many unique values

D A column that is frequently updated

Columns with many unique values help indexes filter data efficiently.

What is a downside of adding too many indexes?

A Slower data retrieval

B Slower data insertion and updates

C More disk space saved

D Queries become less readable

Indexes slow down insert, update, and delete operations because the index must be updated too.

When is a composite index most useful?

A When queries filter on multiple columns together

B When only one column is used in queries

C When columns have many duplicate values

D When the table is very small

Composite indexes speed up queries filtering or sorting by multiple columns.

Which of these is NOT a good candidate for indexing?

A Primary key column

B Column used in JOIN conditions

C Foreign key column

D Column with many NULL values and duplicates

Columns with many NULLs and duplicates do not benefit much from indexing.

How does an index improve query performance?

A By reducing the number of rows the database must check

B By increasing the size of the table

C By compressing the data

D By scanning the entire table faster

Indexes help the database find relevant rows quickly without scanning all rows.

Explain the key factors to consider when selecting columns for indexing.

Think about how indexes help searches and how they affect updates.

Describe the difference between single-column and composite indexes and when to use each.

Consider how queries filter data using one or more columns.

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