When indexes help and when they hurt in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main benefit of using indexes in a database?

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

beginner

When can indexes hurt database performance?

Indexes can slow down data modification operations like INSERT, UPDATE, and DELETE because the index must be updated too.

intermediate

Why might having too many indexes be a problem?

Too many indexes increase storage space and slow down write operations, making the database less efficient overall.

beginner

How do indexes help with query filtering conditions?

Indexes allow the database to quickly locate rows matching filter conditions without scanning every row, improving query speed.

intermediate

When is it not useful to create an index on a column?

Creating an index on columns with very few unique values (low cardinality) often doesn't help because many rows share the same value.

What operation is most negatively affected by indexes?

A CREATE

B SELECT

C JOIN

D INSERT

Indexes slow down INSERT operations because the index must be updated whenever new data is added.

Which of these is a good reason to create an index?

A To reduce storage space

B To speed up queries filtering on a column

C To speed up bulk data inserts

D To avoid updating data

Indexes help queries that filter on specific columns by making data retrieval faster.

What happens if you create too many indexes on a table?

A Write operations become slower

B Storage space decreases

C Queries become slower

D Indexes automatically merge

Too many indexes slow down write operations because each index must be updated.

Which column is least suitable for indexing?

A A column with few unique values

B A foreign key column

C A primary key column

D A column with many unique values

Columns with few unique values (low cardinality) are less useful for indexing because many rows share the same value.

How do indexes improve SELECT queries?

A By scanning the entire table faster

B By reducing the number of columns

C By quickly locating rows matching conditions

D By compressing data

Indexes help SELECT queries by quickly finding rows that match filter conditions without scanning the whole table.

Explain when indexes help improve database performance and when they can cause problems.

Think about read vs write operations and data uniqueness.

Describe how indexes affect SELECT queries and INSERT operations differently.

Consider the cost of maintaining indexes during data changes.