Index selection guidelines in DBMS Theory - Deep Dive

Overview - Index selection guidelines

What is it?

Index selection guidelines are rules and best practices used to decide which database columns should have indexes. Indexes are special data structures that help databases find data quickly without scanning every row. Choosing the right columns to index improves query speed and overall database performance. Without proper index selection, databases can become slow and inefficient.

Why it matters

Indexes speed up data retrieval, which is critical for applications that need quick responses, like websites or banking systems. Without good index selection, queries take longer, causing delays and unhappy users. Poorly chosen indexes can also waste storage and slow down data updates. Following guidelines ensures a balance between fast searches and efficient storage.

Where it fits

Before learning index selection, you should understand basic database concepts like tables, columns, and queries. After mastering index selection, you can learn advanced topics like query optimization, execution plans, and database tuning.

Mental Model

Core Idea

Choosing the right columns to index is like placing signposts on a road to guide travelers quickly to their destination without unnecessary detours.

Think of it like...

Imagine a large library without a catalog. Finding a book would mean checking every shelf. Adding an index is like creating a catalog that tells you exactly where to find each book, saving time and effort.

┌───────────────┐
│   Table Data  │
│  (All rows)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│    Indexes    │
│ (Signposts)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Fast Lookup  │
│ (Direct Access)│
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a database index

Concept: Introduce the basic idea of an index as a tool to speed up data searches.

A database index is like a shortcut that helps the database find rows faster. Instead of looking at every row, the database uses the index to jump directly to the data it needs. Indexes are built on one or more columns of a table.

Result

Queries that use indexed columns run faster because the database avoids scanning the whole table.

Understanding what an index is lays the foundation for knowing why selecting the right columns matters.

2

Foundation

How indexes improve query speed

Concept: Explain the mechanism of how indexes reduce search time.

When a query asks for data based on a column, the database checks if an index exists on that column. If yes, it uses the index to find the exact location of the data quickly, like using a phone book instead of searching every page.

Result

The database spends less time searching, making queries faster and more efficient.

Knowing how indexes speed up queries helps understand why some columns benefit more from indexing.

3

Intermediate

Choosing columns with high selectivity

🤔Before reading on: do you think indexing columns with many repeated values is as effective as indexing unique columns? Commit to your answer.

Concept: Introduce the idea of selectivity, meaning how unique the values in a column are.

Columns with many unique values (high selectivity) are better candidates for indexes because they narrow down searches more effectively. For example, indexing a 'User ID' column is better than indexing a 'Gender' column with only two values.

Result

Indexes on high-selectivity columns improve query speed significantly by filtering data precisely.

Understanding selectivity prevents wasting indexes on columns that don't help narrow down results.

4

Intermediate

Indexing columns used in WHERE clauses

🤔Before reading on: do you think indexing columns not used in search conditions helps queries? Commit to your answer.

Concept: Explain that indexes are most useful on columns frequently used to filter data in queries.

If a column is often used in WHERE conditions to find specific rows, indexing it helps the database quickly locate those rows. Columns rarely used in searches usually don't benefit from indexes.

Result

Queries with filters on indexed columns run faster because the database can quickly find matching rows.

Knowing which columns appear in search conditions guides effective index placement.

5

Intermediate

Using composite indexes wisely

🤔Before reading on: do you think a composite index on multiple columns always improves all queries involving those columns? Commit to your answer.

Concept: Introduce composite indexes that cover multiple columns and their order importance.

A composite index combines several columns into one index. The order of columns matters because the database uses the index starting from the first column. Queries filtering on the first column or the first few columns benefit most. Queries skipping the first column may not use the index efficiently.

Result

Properly ordered composite indexes speed up complex queries filtering on multiple columns.

Understanding column order in composite indexes helps design indexes that match query patterns.

6

Advanced

Balancing read and write performance

🤔Before reading on: do you think adding more indexes always improves overall database performance? Commit to your answer.

Concept: Explain the tradeoff between faster reads and slower writes caused by indexes.

While indexes speed up data retrieval, they slow down data changes like inserts, updates, and deletes because the indexes must be updated too. Adding too many indexes can hurt write performance. Choosing indexes requires balancing query speed with update costs.

Result

A well-balanced index strategy improves overall system performance without excessive slowdowns.

Knowing the cost of maintaining indexes prevents over-indexing and performance degradation.

7

Expert

Index selection in real-world workloads

🤔Before reading on: do you think the best indexes are the same for all applications? Commit to your answer.

Concept: Discuss how workload patterns and query types influence index choices in production systems.

Different applications have different query patterns. For example, a reporting system may benefit from indexes on columns used in aggregations, while a transactional system needs indexes on columns used in frequent lookups. Monitoring query logs and analyzing execution plans help tailor index selection to real usage.

Result

Custom index strategies based on workload improve performance more than generic rules.

Understanding workload-driven index selection is key to expert database tuning.

Under the Hood

Indexes are usually implemented as balanced tree structures (like B-trees) or hash tables. When a query uses an indexed column, the database traverses the index tree to find pointers to the exact rows, avoiding full table scans. Updates to data require updating the index structure to keep it consistent.

Why designed this way?

Indexes were designed to speed up data retrieval in large datasets where scanning every row is too slow. Tree structures balance fast search, insert, and delete operations. Alternatives like full scans were too slow, and hash indexes have limitations on range queries, so B-trees became standard.

┌───────────────┐
│   Query Uses  │
│ Indexed Column│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Index Tree  │
│ (B-tree or HT)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Row Pointers │
│  to Table Data│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Table Rows  │
│ (Actual Data) │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: does indexing every column always make queries faster? Commit to yes or no.

Common Belief: Indexing every column will speed up all queries.

Reality: Too many indexes slow down data updates and consume extra storage, often hurting overall performance.

Why it matters: Over-indexing causes slower inserts, updates, and deletes, leading to poor application responsiveness.

Quick: do indexes speed up all types of queries equally? Commit to yes or no.

Common Belief: Indexes speed up all queries, including those that return large amounts of data.

Reality: Indexes help most when queries filter or search for specific rows, but not when retrieving large portions of the table.

Why it matters: Using indexes for queries that return many rows can add overhead without benefit, wasting resources.

Quick: does the order of columns in a composite index not matter? Commit to yes or no.

Common Belief: The order of columns in a composite index does not affect its usefulness.

Reality: The order is critical; the index is most effective when queries filter on the leading columns in order.

Why it matters: Ignoring column order leads to indexes that are not used by the database, wasting space and effort.

Quick: can indexing low-selectivity columns improve performance significantly? Commit to yes or no.

Common Belief: Indexing columns with few unique values (like gender) greatly improves query speed.

Reality: Low-selectivity columns rarely benefit from indexes because they don't narrow down results well.

Why it matters: Indexing such columns wastes storage and slows down writes without meaningful speed gains.

Expert Zone

1

Partial indexes that index only rows meeting certain conditions can optimize performance for specific queries.

2

Covering indexes include all columns needed by a query, allowing the database to answer queries using only the index without accessing the table.

3

Index fragmentation over time can degrade performance, so regular maintenance like rebuilding or reorganizing indexes is necessary.

When NOT to use

Avoid indexing columns that are frequently updated or have low selectivity. Instead, consider query rewriting, caching, or using materialized views for performance. For full-text search, specialized indexes like inverted indexes are better than standard B-tree indexes.

Production Patterns

In production, DBAs analyze slow query logs and execution plans to identify missing or unused indexes. They use monitoring tools to balance read/write performance and apply indexing strategies like indexing foreign keys, using composite indexes for common multi-column filters, and employing partial or filtered indexes for targeted optimization.

Connections

Caching

Complementary performance technique

Understanding indexing helps grasp how caching stores frequently accessed data to reduce database hits, both aiming to speed up data retrieval.

Data Structures

Indexes are implemented using data structures

Knowing how trees and hash tables work deepens understanding of index performance and limitations.

Library Cataloging Systems

Similar organizational principle

Recognizing that indexes in databases function like library catalogs helps appreciate the universal need for efficient information retrieval.

Common Pitfalls

#1 Indexing every column without analysis

Wrong approach:

CREATE INDEX idx_all_columns ON table(col1, col2, col3, col4);

Correct approach:

CREATE INDEX idx_important_columns ON table(col1);

Root cause:

Misunderstanding that more indexes always mean better performance, ignoring update costs and storage.

#2 Ignoring column order in composite indexes

Wrong approach:

CREATE INDEX idx_wrong_order ON table(col3, col1); -- but queries filter on col1 first

Correct approach:

CREATE INDEX idx_correct_order ON table(col1, col3);

Root cause:

Not aligning index column order with query filter order, leading to unused indexes.

#3 Indexing low-selectivity columns

Wrong approach:

CREATE INDEX idx_gender ON table(gender);

Correct approach:

Avoid indexing gender; focus on high-selectivity columns like user_id.

Root cause:

Belief that any index improves performance, ignoring selectivity impact.

Key Takeaways

Indexes are special data structures that speed up data searches by avoiding full table scans.

Choosing which columns to index depends on how unique their values are and how often they appear in query filters.

Composite indexes must be carefully ordered to match query patterns for maximum effectiveness.

Adding indexes improves read speed but can slow down data updates, so balance is essential.

Real-world index selection requires analyzing actual query workloads and adjusting indexes accordingly.

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