Practise vocabulary for index design discussions: B-tree vs. hash, composite indexes, covering indexes, partial indexes, and index trade-offs.
0 / 5 completed
1 / 5
A ___ index is the default type in most relational databases, efficient for range queries and ORDER BY operations.
B-tree (balanced tree) indexes support equality lookups, range queries (BETWEEN, >, <), and ORDER BY efficiently. They're the default index type in PostgreSQL, MySQL, and SQL Server.
2 / 5
A ___ index on (last_name, first_name) can serve queries filtering on last_name alone, but cannot efficiently serve queries on first_name alone.
A composite index (multi-column index) follows the leftmost prefix rule: it can serve queries using the leading columns in order. (last_name, first_name) works for last_name but not first_name in isolation.
3 / 5
A ___ index only covers a subset of rows — for example, indexing only rows where status = 'active'.
A partial index (PostgreSQL) includes only rows matching a WHERE condition. It's smaller and faster than a full-table index — ideal when queries frequently filter on a selective condition like 'status = active'.
4 / 5
Every index improves ___ performance but degrades ___ performance, since each index must be updated on every write.
Indexes speed up read queries (lookup, range scan) but add overhead to writes (INSERT, UPDATE, DELETE must update all indexes). Over-indexing a write-heavy table degrades throughput — index choice is always a trade-off.
5 / 5
'Table bloat' in the context of indexing refers to ___.
Table and index bloat accumulates dead rows (in PostgreSQL: from MVCC, old versions kept until VACUUM) and dead index entries. VACUUM reclaims this space. Heavy UPDATE/DELETE workloads require regular maintenance to prevent bloat.