Fundamentals of Database Indexing: Composite, Covering Indexes, and EXPLAIN Quiz

Test your understanding of key database indexing concepts, including composite and covering indexes, and how to interpret output from the EXPLAIN command. This quiz is designed to help beginners evaluate their knowledge of efficient query performance, indexing strategies, and query optimization.

1. Purpose of an Index

   What is the primary benefit of using an index in a database table?

   1. To speed up data retrieval operations
   2. To remove duplicate records automatically
   3. To compress all stored data
   4. To encrypt table data for security

   Explanation: Indexes help improve the speed of data retrieval by allowing the database to find rows faster. The other options are incorrect: encryption and compression are not main purposes of indexes, and indexes do not remove duplicates automatically.

2. Composite Index Definition

   Which of the following best describes a composite index in a database?

   1. A backup copy of the primary key
   2. An index on multiple columns
   3. An index on a single numeric column
   4. A compressed version of an index

   Explanation: A composite index is created on two or more columns of a table, enabling queries filtered by those columns to execute more efficiently. An index on a single numeric column is not necessarily composite. A backup copy of the primary key and compressed indexes refer to different concepts.

3. Choosing Indexed Columns

   If you often query a table using conditions on both 'first_name' and 'last_name', what type of index would improve performance the most?

   1. Single index on the entire table
   2. Index only on 'last_name'
   3. Index only on 'first_name'
   4. Composite index on ('first_name', 'last_name')

   Explanation: A composite index on both columns is optimized for queries that filter by both 'first_name' and 'last_name'. Indexing only one column helps only for conditions on that column. A single index on the entire table is not a valid indexing strategy.

4. Covering Index Advantage

   What is the main advantage of a covering index when running SELECT queries?

   1. It automatically repairs corrupt data
   2. The query can be satisfied without reading the table rows
   3. It always uses less disk space
   4. It guarantees uniqueness on the indexed column

   Explanation: A covering index includes all columns referenced in the query, allowing the database to return results directly from the index. Covering indexes do not guarantee uniqueness, nor do they always use less space. They do not repair corrupt data.

5. EXPLAIN Command Usage

   What is the purpose of using the EXPLAIN command in SQL?

   1. To change the structure of a table
   2. To analyze how a query will be executed by the database
   3. To insert new records into a table
   4. To encrypt all table data

   Explanation: The EXPLAIN command shows the execution plan for a query, including which indexes are used and how rows are accessed. It does not alter table structure, insert records, or encrypt data.

6. Interpreting EXPLAIN Output

   If the EXPLAIN output for a query shows 'Using index' in the Extra column, what does this mean?

   1. No index is used for the query
   2. The query results are read entirely from the index without accessing the table
   3. A syntax error is present in the query
   4. The index is being rebuilt

   Explanation: 'Using index' indicates a covering index is in effect, and the table's data rows do not need to be accessed. The other options are incorrect: 'No index' would be stated if not used, syntax errors are not related, and index rebuilding is not shown this way.

7. Unique Indexes

   Which statement is TRUE about a unique index?

   1. It requires at least three columns
   2. It prevents duplicate values in the indexed column(s)
   3. It automatically creates a composite index
   4. It always improves data compression

   Explanation: Unique indexes enforce uniqueness on the indexed columns, preventing duplicate values. They are unrelated to data compression, do not necessarily create composite indexes, and can apply to just one column.

8. Composite Index Order

   Given a composite index on ('city', 'state'), which query will benefit most from this index?

   1. SELECT * FROM addresses WHERE city = 'Paris' AND state = 'TX';
   2. SELECT * FROM addresses WHERE country = 'France';
   3. SELECT * FROM addresses WHERE state = 'TX';
   4. SELECT * FROM addresses WHERE zip = '75000';

   Explanation: With a composite index on ('city', 'state'), queries filtering both columns in order will benefit most. Filtering solely on 'state' isn’t as optimal due to index ordering. The other queries do not use the indexed columns.

9. Non-Clustered Indexes

   Which feature accurately describes a non-clustered index?

   1. It prevents updates to indexed columns
   2. It stores all table data in sorted order
   3. It automatically compresses index data
   4. It maintains a separate structure alongside the data table

   Explanation: A non-clustered index is stored separately from the actual table and points to the data rows. It does not sort the whole table, prevent updates, or automatically compress data.

10. Index Downsides

    What is a potential drawback of adding too many indexes to a single table?

    1. All indexes will be combined into one
    2. Indexes will automatically delete older data
    3. Queries will always disregard indexes
    4. Insert and update operations can become slower

    Explanation: Each additional index must be maintained during insert or update, slowing those operations. Queries do not ignore all indexes, indexes are not combined automatically, and they do not delete data.