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.