Index selection guidelines in DBMS Theory - Step-by-Step Execution

Concept Flow - Index selection guidelines

Identify Query Patterns

↓

Check Column Usage in WHERE/JOIN

↓

Evaluate Column Selectivity

↓

Decide Index Type (Single/Multi-column)

↓

Create Index

↓

Monitor Query Performance

↓

Adjust or Drop Index if Needed

This flow shows how to choose columns for indexing by analyzing queries, checking column usage, evaluating selectivity, creating indexes, and monitoring performance.

Execution Sample

DBMS Theory

SELECT * FROM employees WHERE department_id = 5;
-- Index on department_id helps

SELECT * FROM employees WHERE last_name = 'Smith';
-- Index on last_name helps

SELECT * FROM employees WHERE salary > 50000;
-- Index may help if selective

These queries show how indexes on columns used in WHERE clauses can speed up data retrieval.

Analysis Table

Step	Action	Column Evaluated	Selectivity	Index Decision	Reason
1	Analyze query	department_id	High (few departments)	Create index	Used in WHERE, high selectivity
2	Analyze query	last_name	Medium (many unique names)	Create index	Used in WHERE, moderate selectivity
3	Analyze query	salary	Low (many salaries > 50000)	Consider index	Range query, less selective
4	Create index	department_id	N/A	Index created	Improves equality search
5	Create index	last_name	N/A	Index created	Improves equality search
6	Monitor queries	All	N/A	Adjust if needed	Check if indexes help performance
7	Drop index	salary	N/A	Index not created	Low benefit, maintenance cost high

💡 Index decisions made based on column usage and selectivity; low benefit indexes removed

State Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
department_id index	None	Considered	Considered	Considered	Created	Exists
last_name index	None	None	Considered	Considered	Created	Exists
salary index	None	None	None	Considered	Considered	Not created

Key Insights - 3 Insights

Why do we create an index on department_id but not on salary?

Because department_id has high selectivity (few departments), making the index efficient (see execution_table rows 1 and 3). Salary has low selectivity for the query, so the index is less useful and later dropped (rows 3 and 7).

What does 'selectivity' mean in index selection?

Selectivity means how many unique values a column has. High selectivity means many unique values, which makes indexes more effective (refer to execution_table column 'Selectivity').

Why monitor query performance after creating indexes?

Because indexes can improve or sometimes slow down queries. Monitoring helps decide if an index should be kept or dropped (see execution_table rows 6 and 7).

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the index on last_name created?

A Step 5

B Step 2

C Step 4

D Step 7

💡 Hint

Check the 'Index Decision' column for last_name in execution_table rows

According to variable_tracker, what happens to the salary index after step 4?

A It is created then dropped

B It is created and remains

C It is never created

D It is created and monitored

💡 Hint

Look at the 'salary index' row in variable_tracker after step 4 and final

If department_id had low selectivity, what would likely change in the execution_table?

A Index would still be created

B Index decision would be to consider or drop

C No change in index decision

D Index would be created on salary instead

💡 Hint

Refer to how selectivity affects index decision in execution_table rows 1 and 3

Concept Snapshot

Index Selection Guidelines:
- Analyze queries to find columns used in WHERE/JOIN
- Evaluate column selectivity (unique values ratio)
- Create indexes on high-selectivity columns
- Use single or multi-column indexes as needed
- Monitor performance and adjust indexes
- Drop indexes with low benefit to save resources

Full Transcript

Index selection guidelines help decide which columns to index for faster queries. First, look at query patterns and find columns used in WHERE or JOIN clauses. Then check how selective these columns are—columns with many unique values are better for indexing. Create indexes on these columns to speed up searches. After creating indexes, watch query performance to see if they help. If an index does not improve performance or is costly to maintain, consider dropping it. This process ensures indexes improve database speed without wasting resources.

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