Database Indexing — Concept Check
Questions on Database Indexing. An index is a data structure (usually B-Tree or hash) that speeds up reads by avoiding full table scans. Proper indexing is the single biggest performance lever for database-backed systems.
Questions in this quiz
8 questions on Database Indexing — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.Your database queries are slowing down. You notice that a query filtering by a non-indexed column takes significantly longer. What should you do to improve performance?
- AIncrease the database server's RAM.
- BCreate a B-Tree index on the column used in the WHERE clause.
- CAdd more columns to the SELECT statement.
- DCreate a hash index on the column used in the WHERE clause.
2.A query on your orders table using a composite index on (user_id, status, created_at) is slow. The query only filters by status and created_at. Why is the index not used effectively?
- AThe query skips the user_id, violating the leftmost prefix rule.
- BThe query should use a hash index for faster lookups.
- CThe index needs to be rebuilt for better performance.
- DThe index should include more columns for better coverage.
3.You have a hash index on a user session token. What kind of query would benefit most from this index?
- AA query sorting sessions by user_id.
- BA query filtering sessions by creation date range.
- CA query fetching all sessions with a specific status.
- DA query checking if a specific token exists.
4.A developer claims that adding more indexes will always speed up database reads. What is the flaw in this reasoning?
- AIndexes slow down writes and consume disk space.
- BIndexes are only useful for small tables.
- CIndexes require constant manual updates.
- DIndexes only speed up writes, not reads.
5.Your analytics team reports slower query performance after adding several indexes to a write-heavy table. What is a likely cause?
- AThe indexes are not covering all query columns.
- BIndexes are not being used by the queries.
- CIncreased write latency due to index maintenance.
- DThe database server needs more memory.
6.What is a key advantage of a covering index?
- AIt eliminates the need for table lookups by including all queried columns.
- BIt supports range queries more efficiently than B-Tree indexes.
- CIt requires less disk space than other index types.
- DIt automatically updates without maintenance.
7.A query using a B-Tree index on the 'created_at' column is slow. What could be a reason if the index is used?
- AThe query should use a hash index instead.
- BThe index is fragmented and needs reindexing.
- CThe B-Tree index does not support sorting.
- DThe database server is out of disk space.
8.A query on a table with 10 indexes is slow when inserting new records. What is the likely reason?
- AThe table is too small to benefit from indexing.
- BThe indexes are not used in the query.
- CThe database server needs more CPU power.
- DEach insert operation updates all indexes, slowing down the process.