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