QuizzesComposite Index & Column Order — Concept Check
intermediate
sql

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.

7 minutes 7 questions Pass at 70%
Time Limit
7 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

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. 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. 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. 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. 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. 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. 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. 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.