Database indexing in HLD - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a database index?

A database index is a data structure that helps speed up data retrieval by allowing the database to find rows faster without scanning the entire table.

beginner

How does an index improve query performance?

An index works like a book's index: it points to the location of data, so the database can jump directly to the needed rows instead of checking every row.

intermediate

What are the common types of database indexes?

Common types include B-tree indexes for range queries, hash indexes for exact matches, and bitmap indexes for columns with few unique values.

intermediate

What is a trade-off when using indexes?

Indexes speed up reads but slow down writes because the index must be updated whenever data changes, and they also use extra storage space.

intermediate

What is a composite index?

A composite index is an index on multiple columns, which helps queries that filter or sort by more than one column efficiently.

Which data structure is commonly used for database indexes to support range queries?

A B-tree

B Hash table

C Linked list

D Stack

B-tree indexes allow efficient range queries by keeping data sorted and balanced.

What is a downside of adding many indexes to a database table?

A Loss of data integrity

B Slower data retrieval

C Reduced query flexibility

D Increased storage and slower writes

More indexes require extra storage and slow down insert, update, and delete operations.

Which type of index is best for exact match queries?

A Hash index

B B-tree index

C Bitmap index

D Full-text index

Hash indexes provide fast lookups for exact matches but are not suitable for range queries.

What does a composite index index?

A A single column

B Multiple columns

C Only primary keys

D Only foreign keys

Composite indexes cover multiple columns to optimize queries filtering on those columns.

Why might a database choose not to use an index for a query?

A The query is very complex

B The index is corrupted

C The table is very small

D The database is offline

For small tables, scanning all rows can be faster than using an index.

Explain how database indexes improve query speed and what trade-offs they introduce.

Think about how a book index helps find pages faster but takes space.

Describe different types of database indexes and when to use each.

Consider the kind of queries and data uniqueness.