Database Indexing Basics: Composite, Covering Indexes, and Query Plans Quiz

Test your knowledge of database indexing basics, including composite and covering indexes, and understanding EXPLAIN/query plans. This quiz helps users identify key concepts, best practices, and definitions crucial for efficient database query performance.

1. Composite Indexes Functionality

   Which statement best describes a composite index in a relational database?

   1. An index that only supports unique values
   2. An index based on primary key columns only
   3. An index created automatically by the system
   4. An index that includes two or more columns from the same table

   Explanation: A composite index is one that covers two or more columns from a table to improve multi-column query performance. While primary keys can be indexed, not all composite indexes are primary keys. They are not created automatically by the system but by user command, and they are not limited to unique values by default, unlike unique indexes or constraints.

2. Covering Indexes Definition

   What is a covering index in the context of query optimization?

   1. An index that only stores primary key columns
   2. An index that is inactive by default
   3. An index that duplicates another existing index
   4. An index that contains all the columns needed to fulfill a query

   Explanation: A covering index includes all the columns required to satisfy a query without needing to access the underlying table records. It is not a duplicate of another index or limited to primary keys. Covering indexes are active once created and used by the query planner when advantageous.

3. EXPLAIN Output Interpretation

   When examining an EXPLAIN plan, what does the term 'index scan' usually mean?

   1. The query is scanning all rows in the table without using an index
   2. The query is skipping the index entirely
   3. The query is rebuilding the index
   4. The query is using an index to find matching rows

   Explanation: 'Index scan' means the query engine is using an existing index to retrieve relevant rows, enhancing efficiency. Table scans indicate no index usage, while skipping an index means the engine does not utilize it at all. Rebuilding the index refers to maintenance tasks, not query execution.

4. When Composite Indexes Help

   For which type of queries does a composite index on columns (A, B) offer the best performance benefit?

   1. Queries using only column C in the WHERE clause
   2. Queries that only count rows
   3. Queries that update the table schema
   4. Queries filtering or sorting by both A and B

   Explanation: Composite indexes are most efficient when queries filter, sort, or search by both columns included in the index, such as (A, B). An index on (A, B) does not help with queries filtering only by column C. Changing table schemas or row counts doesn't directly relate to index usage.

5. Avoiding Redundant Indexes

   If you have an index on columns (first_name, last_name), what will happen if you also create a separate index on first_name alone?

   1. It will convert both to unique indexes
   2. It will remove the composite index
   3. It may result in a redundant index and waste storage
   4. It will automatically improve all query speeds

   Explanation: Having both a composite index (first_name, last_name) and a single-column index on first_name may not benefit performance and could consume extra space unnecessarily. Creating additional indexes doesn't always increase speed, doesn't remove existing indexes, and doesn't convert them into unique indexes by default.

6. Unique vs Non-Unique Indexes

   What distinguishes a composite unique index from a regular composite index?

   1. A unique index is always faster
   2. A unique index prevents queries from running
   3. A unique index enforces that the combination of column values is unique
   4. A unique index can only be single-column

   Explanation: A composite unique index requires unique combinations across the indexed columns, ensuring data integrity. Regular composite indexes do not enforce uniqueness. Uniqueness does not inherently make an index faster or single-column, nor does it block queries from executing.

7. Index Coverage in SELECT Queries

   In a query such as SELECT id, name FROM users WHERE age = 30, what would make an index on (age, id, name) a covering index?

   1. The index is unique
   2. All columns needed by the query are present in the index
   3. The index is marked as partial
   4. The index is only on the id column

   Explanation: A covering index contains every column referenced in the query, so (age, id, name) allows the query to be answered entirely from the index. An index on one column, or a partial or unique index, does not guarantee coverage of all columns needed.

8. Primary Key and Indexing

   What is the typical purpose of a primary key index in a relational database?

   1. To slow down query performance
   2. To store data redundantly across tables
   3. To uniquely identify each record and facilitate efficient lookups
   4. To prevent the use of covering indexes

   Explanation: Primary key indexes ensure each record in a table is unique and allow fast, reliable data retrieval. They do not cause redundant storage, block covering indexes, or hinder query speed; in fact, they boost efficiency.

9. Index Order Importance

   Why does the order of columns in a composite index matter?

   1. Because indexes are always unordered
   2. Because only the last column is ever used
   3. Because the order affects which queries benefit from the index
   4. Because it changes the names of the columns

   Explanation: Column order in a composite index determines which combinations of query filter conditions can use the index. The notion that all indexes are unordered, that only the last column is used, or that it directly alters column names, is incorrect.

10. Reading Query Plans for Index Usage

    When viewing a query plan, which clue indicates that a covering index is being used?

    1. The plan indicates data is fetched from cache
    2. The plan shows only index access with no table access
    3. The plan lists a cartesian join
    4. The plan shows a full table scan

    Explanation: A covering index is utilized when the plan indicates rows are returned by accessing just the index without accessing the underlying table rows. A full table scan or a cartesian join suggests inefficient query execution, and cache access is not specifically related to covering indexes.

11. Composite Indexes and Query Order

    If you have a composite index on (A, B), which WHERE clauses will the index help the most?

    1. WHERE B = ? AND A = ?
    2. WHERE D u003E ?
    3. WHERE C = ?
    4. WHERE A = ? AND B = ?

    Explanation: A composite index on (A, B) is most effective with queries that filter first by column A, then by B in that sequence. Swapping the order in the WHERE clause may not optimize as well, depending on the database's optimizer. Queries on C or D do not match the indexed columns.

12. Partial Index Match

    If a query filters only by the first column in a composite index, can the index still be used?

    1. No, it only works if all columns are specified
    2. No, the index is ignored if not all columns are used
    3. Yes, it can use the index for filtering by the first column
    4. Yes, but only if the index is unique

    Explanation: An index on (A, B) can speed up queries filtering by A only, as the index is ordered starting with A. The index won't be ignored for single-column queries, and uniqueness is not required for this feature. Specifying all columns is not mandatory for index usage.

13. EXPLAIN and Index Selection

    What should you examine in an EXPLAIN plan to determine if a composite index is being used?

    1. Look for only full table scans
    2. Look for the name of the composite index in the plan output
    3. Check for duplicate keys in the result set
    4. Look for syntax errors in the query

    Explanation: EXPLAIN plans often show the name of the index employed by the query, which identifies composite index usage. Table scans mean indexes are not used, duplicate keys relate to logic errors, and syntax errors do not relate to index selection.

14. Choosing Indexes for SELECT Queries

    Which index would be most effective for this query: SELECT email FROM customers WHERE first_name = 'Sam' AND last_name = 'Smith'?

    1. A composite index on (first_name, last_name, email)
    2. An index on customer ID only
    3. No index at all
    4. An index only on email

    Explanation: A composite index on the columns used in WHERE and SELECT clauses, like (first_name, last_name, email), can serve as a covering index and offer the best performance. Indexes on unrelated columns or omitting indexing altogether will not help this query.

15. Over-Indexing Risks

    What is a potential disadvantage of creating many indexes on a single table?

    1. It guarantees the fastest possible queries
    2. It automatically removes duplicate data
    3. It reduces available columns in the table
    4. It may slow down insert and update operations

    Explanation: Having too many indexes can decrease insert and update performance, as each change may require updating multiple indexes. Many indexes do not always guarantee speed, do not eliminate duplicate data, and have no effect on the number of columns in a table.

16. Incorrect Index Usage

    If a query plan shows a full table scan despite the presence of an index, what might be one cause?

    1. Indexes are always ignored in all queries
    2. The columns are spelled correctly
    3. Indexes are disabled by default
    4. The query conditions do not match the indexed columns

    Explanation: A table scan occurs when none of the query's filter conditions match the indexed columns, making the index ineffective. Indexes are not always ignored or disabled by default, and correct column spelling does not ensure index usage if query logic does not align with indexed fields.