QuizzesIndex Selectivity & Cardinality — Concept Check
intermediate
mysql

Index Selectivity & Cardinality — Concept Check

Questions on Index Selectivity & Cardinality. High selectivity (many distinct values) makes an index effective; cardinality statistics in information_schema.statistics guide the optimiser's index selection decisions.

8 minutes 8 questions Pass at 70%
Time Limit
8 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

8 questions on Index Selectivity & Cardinality — Concept Check. Work through them here, then start the quiz to answer them and see your score.

  1. 1.A query on the 'orders' table uses the 'status' index and returns 20% of rows. What is likely happening?

    • AThe optimizer prefers a full table scan over the index due to low selectivity.
    • BThe index is not used because it is a composite index.
    • CThe index is used because it has high cardinality.
    • DThe optimizer uses the index because it is highly selective.
  2. 2.You run ANALYZE TABLE on 'orders' after a bulk data load. Why is this necessary?

    • ATo refresh cardinality statistics for better optimizer decisions.
    • BTo remove unused indexes from the table.
    • CTo rebuild all indexes for improved performance.
    • DTo increase the selectivity of all indexes.
  3. 3.A query on 'orders' uses EXPLAIN and shows 'key: NULL'. What does this indicate?

    • AThe optimizer chose a full table scan over an index lookup.
    • BThe index is being forced by the query.
    • CThe query is using a composite index.
    • DThe optimizer has no statistics for the table.
  4. 4.Which index would the optimizer likely use for a query filtering by 'customer_id'?

    • AAn index on 'customer_id' due to its high selectivity.
    • BA composite index starting with 'status'.
    • CA prefix index on 'customer_id'.
    • DAn index on 'status' due to its low cardinality.
  5. 5.A composite index on (status, created_at) is used in a query. Why is this efficient?

    • ABoth columns have high cardinality.
    • B'created_at' is a prefix index.
    • CThe leading column 'status' filters, and 'created_at' provides ordering.
    • DThe index is used for ORDER BY only.
  6. 6.Why might a prefix index on a VARCHAR column be used?

    • ATo reduce storage while maintaining most of the selectivity.
    • BTo increase the cardinality of the column.
    • CTo ensure the index is used for all queries.
    • DTo improve ORDER BY performance.
  7. 7.A query on 'orders' shows 'type: ALL' in EXPLAIN. What does this mean?

    • AThe optimizer prefers a full table scan over any indexes.
    • BThe query is using a composite index.
    • CThe optimizer has no statistics for the table.
    • DThe query is using all available indexes.
  8. 8.What happens if an index lookup reads more than 30% of rows?

    • AThe optimizer will always use the index.
    • BThe optimizer may prefer a full table scan instead.
    • CThe index is automatically rebuilt.
    • DThe index is considered highly selective.