Key Concepts in Database Indexing: Composite, Covering Indexes, and Query Plans Quiz

Discover essential database indexing concepts, including composite indexes, covering indexes, and query plan basics. This quiz helps reinforce knowledge of indexing strategies, optimizing select queries, and understanding how query plans impact database performance.

1. Definition of Composite Index

   Which statement best describes a composite index in a database?

   1. An index that covers all columns in a table
   2. An index based on a formula rather than specific columns
   3. An index that can only be used for primary keys
   4. An index that involves two or more columns in a single index structure

   Explanation: A composite index is built on two or more columns, allowing efficient access to records using combinations of those columns. It is not required to cover all table columns, making option two incorrect. Indexes based on formulas are known as expression or function-based indexes, not composite. Primary keys can use indexes, but composite indexes are not exclusive to primary keys.

2. Purpose of a Covering Index

   In database indexing, what is the primary benefit of using a covering index for a SELECT query?

   1. It speeds up all INSERT operations
   2. It increases storage requirements for the database tables
   3. It allows the query to be answered entirely using the index without accessing the table data
   4. It only speeds up DELETE operations

   Explanation: A covering index contains all the columns the query needs, so the database engine can satisfy the query using only the index, improving performance. INSERTs are not typically optimized by covering indexes, so option two is incorrect. While indexes do use some storage, the main benefit is query performance, not storage cost. Covering indexes are not specific to DELETE operations.

3. Query Plan Usage

   Why is it important for a database user to review the query plan when troubleshooting slow queries?

   1. It provides authentication details for users
   2. It shows how the database executes the query, revealing inefficiencies and index use
   3. It only shows table structures
   4. It displays the database version

   Explanation: The query plan details the sequence and methods the database uses to retrieve data, helping identify slow operations and improper index usage. Query plans do not show software versions or authentication, making options two and three wrong. They also show more than basic table structures, so option four is incorrect.

4. Selecting Columns in Composite Index

   When designing a composite index for queries filtering by columns A and B, which key strategy usually improves index effectiveness?

   1. Include all columns from the table in the composite index
   2. Put numeric columns before text columns, regardless of usage
   3. Place the most frequently filtered column as the first part of the composite index
   4. Always use alphabetical order for composite indexes

   Explanation: Placing the most frequently filtered column first aligns the index with actual query patterns, making lookups more efficient. Alphabetical order has no effect unless it matches usage, so option two is wrong. Including all columns increases index size unnecessarily, so option three is less appropriate. Numeric columns should not be prioritized unless queries filter by them most often.

5. Index Suffix Matching

   Given a composite index on columns (name, age), which of the following will fully utilize this index?

   1. A query using OR between name and age
   2. A query filtering only by age
   3. A query filtering by an unrelated column, like salary
   4. A query filtering by both name and age

   Explanation: To fully utilize a composite index on (name, age), a query should filter by both columns in order. Filtering by age alone does not match the index prefix, so option two is incorrect. Unrelated columns don't use this index, making option three wrong. OR operations may not fully leverage composite indexes unless the database engine supports it for both columns.

6. Covering Index Limitation

   Why might a SELECT query fail to benefit from a covering index even if one exists on related columns?

   1. The index is set to unique
   2. The query requests columns not included in the index
   3. Covering indexes cannot be used for SELECT queries
   4. The index has too many rows

   Explanation: If a covering index does not include all columns needed by the SELECT query, the database must access the table, making the index less effective. The number of rows in an index does not determine if it is covering, so option two is wrong. Covering indexes are specifically optimized for SELECT queries, making option three invalid. Whether an index is unique or not does not affect its covering capabilities.

7. Impact of Indexes on Query Performance

   How can the presence of indexes most directly improve SELECT query performance in relational databases?

   1. By preventing table scans from ever occurring
   2. By making every query execute in the same amount of time
   3. By reducing the amount of data scanned to locate relevant rows
   4. By increasing disk space usage only

   Explanation: Indexes enable the database to find data more efficiently, shrinking the amount of data scanned for SELECT queries. While indexes use additional disk space, that's not their performance impact, so option two is wrong. Queries do not always take the same time, as it depends on query design and data, so option three is incorrect. Table scans can still happen in some cases, making option four wrong.

8. Composite Index Example

   Which of the following best illustrates a composite index?

   1. An index on a computed value from city
   2. An index on both (city, state) in a single index structure
   3. An index only on the primary key
   4. A separate individual index on city and another on state

   Explanation: A composite index combines two or more columns, such as city and state, into a single index. An index on just a primary key is not composite, making option two wrong. Separate indexes on city and state are not composite—they are single-column indexes. A computed-value index is not the same as a composite index.

9. Query Plan Visualization

   What information does a database query plan typically reveal to users analyzing query performance?

   1. User permissions and security roles
   2. The chosen indexes, join types, and the sequence of data access operations
   3. The date the database was last backed up
   4. Only the SQL text of the query

   Explanation: A query plan explains the database engine's step-by-step approach, including chosen indexes, join methodologies, and data access order. While the SQL query is important, query plans are more than just text, making option two wrong. User permissions and backup dates are unrelated to query plans, so options three and four are incorrect.

10. Selecting a Covering Index

    Suppose a SELECT query retrieves columns customer_id and email, filtering on customer_id; which index structure would make this a covering index?

    1. An index only on email
    2. An index only on age
    3. An index on (name, address)
    4. An index on (customer_id, email)

    Explanation: A covering index must include all columns retrieved by the query; thus, (customer_id, email) allows the query to be satisfied entirely by the index. Email alone does not cover customer_id filtering, making option two wrong. Name and address are unrelated, so option three is incorrect. Age is not used by the query, making option four a poor fit.