Creating Indexes — Concept Check
Questions on Creating Indexes. Indexes speed up read queries by creating an ordered data structure; CREATE INDEX, UNIQUE INDEX, and composite indexes must be chosen deliberately to avoid write overhead.
Questions in this quiz
8 questions on Creating Indexes — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A database administrator notices that write operations have slowed down after adding several indexes to a table. What is the most likely cause?
- AOver-indexing increases write overhead.
- BIndexes are not named explicitly.
- CIndexes are on low-cardinality columns.
- DIndexes are not being used in queries.
2.Which of the following queries can utilize a composite index on columns (a, b, c)?
- AWHERE b = 2 AND c = 3
- BWHERE a = 1 AND b = 2
- CWHERE b = 2
- DWHERE c = 3
3.A developer creates an index on a boolean column with only two distinct values. What is the likely outcome?
- AThe index will reduce table size.
- BThe index will improve write performance.
- CThe index will rarely be used.
- DThe index will speed up all queries.
4.A team wants to ensure email uniqueness in their users table. Which index should they use?
- ACREATE UNIQUE INDEX idx_users_email ON users(email);
- BCREATE INDEX idx_users_email ON users(email);
- CCREATE INDEX idx_users_is_active ON users(is_active);
- DCREATE INDEX idx_users_id ON users(id);
5.A PostgreSQL user wants to build an index without locking writes. Which command should they use?
- ACREATE UNIQUE INDEX idx_orders_product_id ON orders(product_id);
- BCREATE INDEX CONCURRENTLY idx_orders_product_id ON orders(product_id);
- CCREATE INDEX idx_orders_product_id ON orders(product_id);
- DCREATE INDEX idx_orders_product_id ON orders(product_id) WITH NOLOCK;
6.Which scenario is a poor candidate for indexing?
- AA column with only three distinct status values.
- BA foreign key column in a large table.
- CA column frequently used in JOIN operations.
- DA high-cardinality column used in WHERE clauses.
7.Why might an index on a column used only with a function be ineffective?
- AThe index reduces the need for sequential scans.
- BThe index increases read performance.
- CThe function prevents the index from being used.
- DThe index decreases write performance.
8.Why should the most selective column be listed first in a composite index?
- ATo maximize the index's filtering efficiency.
- BTo ensure the index is always used.
- CTo reduce the index size.
- DTo improve write performance.