Composite Index & Column Order — Concept Check
Questions on Composite Index & Column Order. A composite index covers multiple columns, but only queries that reference the leftmost prefix of the index columns can use it — column order is a critical design decision.
Questions in this quiz
7 questions on Composite Index & Column Order — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.You have a composite index on (user_id, created_at) and a query filtering only on created_at. What happens?
- AA bitmap index scan is used to optimize the query.
- BAn index scan is used because created_at is indexed.
- CA sequential scan is used because the leading column is skipped.
- DThe query fails due to missing index.
2.A query on a table with an index (status, created_at) filters on status = 'active' and created_at > '2023-01-01'. What is true?
- AThe index is used efficiently, as equality precedes range.
- BThe index is ignored and a sequential scan occurs.
- CThe index is not used because created_at is a range condition.
- DThe index is used but inefficiently due to the range condition.
3.A composite index on (city, status) is created. Why might this be preferred over (status, city)?
- ACity likely has higher cardinality, improving index efficiency.
- BCity is less important and should be second.
- CStatus is a range condition and should be last.
- DStatus is more important, so it should come first.
4.Given an index on (department_id, salary), which query benefits from it?
- AWHERE salary > 50000
- BWHERE department_id > 5
- CWHERE salary = 50000 AND department_id = 5
- DWHERE department_id = 5 AND salary > 50000
5.Why might an index on (is_active, name) perform poorly?
- AIndexes cannot include boolean columns.
- BThe index is too complex for the database to handle.
- CName should always be the first column in an index.
- DIs_active has low cardinality, reducing branching efficiency.
6.A query uses an index on (created_at, status) but filters on status first. Why might this be suboptimal?
- AThe index is used efficiently, as both columns are indexed.
- BThe optimizer uses a sequential scan due to wrong column order.
- CThe optimizer uses a bitmap index scan instead.
- DThe query fails because the index order is incorrect.
7.What happens if a query filters on a non-leading column of a composite index?
- AA partial index is created automatically.
- BThe query fails due to incorrect index usage.
- CA sequential scan is performed.
- DAn index scan is performed on the non-leading column.