Composite Index — Concept Check
Questions on Composite Index. A composite index on (a, b, c) supports queries on (a), (a, b), and (a, b, c) due to leftmost prefix matching; column order matters — put high-cardinality, equality columns first.
Questions in this quiz
8 questions on Composite Index — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A query on the 'orders' table filters by 'status' and 'created_at'. Which index order is optimal?
- A(status, created_at)
- B(created_at, user_id)
- C(user_id, created_at)
- D(created_at, status)
2.A composite index is created on (status, created_at, user_id). Which query can fully utilize this index?
- ASELECT * FROM orders WHERE user_id = 42 AND created_at > '2025-01-01';
- BSELECT * FROM orders WHERE created_at > '2025-01-01';
- CSELECT * FROM orders WHERE user_id = 42;
- DSELECT * FROM orders WHERE status = 'PAID';
3.Why might a composite index on (is_active, email) be inefficient?
- A'is_active' has low cardinality, reducing index efficiency.
- BComposite indexes are always inefficient.
- C'is_active' should be a separate index.
- D'email' should always be first in any index.
4.A query on the 'orders' table uses 'user_id' equality and 'created_at' range. Which index order is best?
- A(status, user_id)
- B(created_at, status)
- C(user_id, created_at)
- D(created_at, user_id)
5.A query uses 'status' and 'user_id' with an OR condition. Why might a composite index not help?
- AComposite indexes don't optimize OR conditions.
- BComposite indexes only work with AND conditions.
- COR conditions always require full table scans.
- DOR conditions use index merge, not composite indexes.
6.What happens if a composite index on (status, created_at, user_id) is queried with only 'created_at'?
- AA full table scan occurs.
- BOnly 'created_at' is used from the index.
- CThe query fails.
- DThe index is fully utilized.
7.Why should equality filter columns be placed before range columns in a composite index?
- ATo avoid using index merge.
- BTo prioritize range columns.
- CTo ensure all columns are used equally.
- DTo maximize B-tree scan efficiency.
8.A service slows and EXPLAIN shows 'type: ALL'. What might this indicate about the index usage?
- AIndex merge is being used.
- BThe index is partially used.
- CThe index is fully utilized.
- DA full table scan is occurring.