Database Indexing Essentials: Composite and Covering Indexes, EXPLAIN u0026 ANALYZE Quiz

Test your knowledge of database indexing basics, including composite and covering indexes, as well as how to read EXPLAIN and ANALYZE outputs. This beginner-friendly quiz helps you grasp essential indexing concepts and interpret query plans for efficient database performance.

1. Composite Indexes: Definition

   What is a composite index in the context of databases?

   1. An index with only a single column
   2. An index that covers multiple columns of a table
   3. An index created for foreign key only
   4. An index that uses a compressed storage format

   Explanation: A composite index is defined as an index that involves more than one column of a table, helping speed up queries that filter on those columns. Compressed storage and foreign keys are unrelated to the concept of composite indexes. A single-column index is not composite by definition.

2. Covering Index Concept

   When is an index called a 'covering index' for a query?

   1. When the index is built with no NULL values
   2. When the index contains all the columns referenced in the query
   3. When the query uses only indexed columns in WHERE clause
   4. When the index is unique for each row

   Explanation: A covering index is one where all columns used in the query (SELECT, WHERE, etc.) are present in the index, so the database does not need to refer to the actual table. The uniqueness of an index or the exclusion of NULLs does not make an index covering. Only using indexed columns in the WHERE clause doesn't guarantee covering unless all selected columns are indexed as well.

3. Understanding EXPLAIN Output

   What primary information does the EXPLAIN command provide for a database query?

   1. The detailed definition of all indexes in the database
   2. The structure of the table being queried
   3. The actual time taken to execute the query
   4. The query execution plan, showing how data will be accessed

   Explanation: EXPLAIN helps visualize the steps the database engine plans to take when executing a query, including which indexes will be used. It doesn't provide timing information (that’s typically shown with ANALYZE) or show table structure or a full list of index definitions.

4. Benefits of Composite Indexes

   Which type of query benefits most from a composite index on (first_name, last_name)?

   1. A query with WHERE first_name = 'Ann' AND last_name = 'Lee'
   2. A query only selecting last_name
   3. A query that updates the primary key
   4. A query using only an ORDER BY clause on another column

   Explanation: Composite indexes are most effective when the WHERE clause matches the columns in the order the index is defined. Selecting only last_name or updating the primary key does not leverage the composite index properly. Sorting by another column isn't directly helped by this index either.

5. EXPLAIN/ANALYZE and Index Usage

   How can EXPLAIN or ANALYZE output help determine if a query uses an index?

   1. By displaying a count of rows in the table
   2. By describing SQL syntax errors
   3. By indicating in the plan node if an index is used for data retrieval
   4. By showing only the structure of the database

   Explanation: In the query plan, EXPLAIN or ANALYZE often specify terms like 'Index Scan' or 'Using Index' if an index is used. They do not display database structure, count rows, or detect syntax errors. Syntax errors are found before query planning, not through EXPLAIN or ANALYZE.

6. Composite Index Column Order

   Why does the order of columns matter in a composite index (e.g., (city, age))?

   1. Because column order affects storage engine encryption
   2. Because queries must filter or sort on leading columns to benefit from the index
   3. Because only the last column in the index is used for filtering
   4. Because indexes can't be created on numeric columns

   Explanation: The most selective (distinct) columns should be first, and queries should filter or order by the leading columns to benefit. Indexes can be created on numeric columns, and the last column alone does not provide index benefit if leading ones aren't filtered. Encryption is unrelated to column order in indexes.

7. Index Types and Performance

   Which index type generally helps the most in speeding up SELECT queries that retrieve specific rows?

   1. B-tree index
   2. Full table scan
   3. Unique constraint
   4. Hash map index

   Explanation: B-tree indexes are efficient for retrieving specific rows and are the default for most relational databases. Hash map indexes are less common and typically for equality comparisons only. A unique constraint is a rule, not a search aid, and a full table scan is not an index at all and is generally slower.

8. Purpose of a Covering Index

   Why do covering indexes often speed up query performance?

   1. Because they only allow unique values per row
   2. Because they store data outside the database
   3. Because they automatically compress all data
   4. Because they eliminate the need to read the table rows if all required data is in the index

   Explanation: When a covering index is used, the database can return query results using just the index, skipping extra reads of table rows. Compression, enforcing uniqueness, or storing data outside the database are not features of covering indexes.

9. Reading a Query Plan Example

   If an EXPLAIN plan shows 'Index Scan using idx_student_name', what does this indicate?

   1. The query is performing a full table scan
   2. The index is being rebuilt
   3. The query will use the idx_student_name index to locate relevant rows
   4. The table does not have any indexes

   Explanation: An 'Index Scan' specifies that the mentioned index, here idx_student_name, is involved in retrieving query results. If it were a full table scan, it would be labeled as such. Index rebuilding is unrelated to query planning, and the presence of the index shows indexes do exist.

10. Matching Queries to Composite Indexes

    Given a composite index on (country, city), which query would most benefit from this index?

    1. SELECT * FROM places WHERE region = 'Europe'
    2. SELECT * FROM places WHERE country = 'France' OR city = 'Paris'
    3. SELECT * FROM places WHERE city = 'Paris'
    4. SELECT * FROM places WHERE country = 'France' AND city = 'Paris'

    Explanation: This query filters by both country and city, matching the index column order and allowing efficient searching. Filtering only by city is not as efficient unless country is included. The region column isn't indexed, and OR queries usually do not effectively use composite indexes.

11. EXPLAIN/ANALYZE For Query Optimization

    How does ANALYZE differ from EXPLAIN when running a query?

    1. ANALYZE is only for altering table statistics
    2. ANALYZE runs the query and provides actual timing and row counts, whereas EXPLAIN shows only the planned steps
    3. EXPLAIN provides execution time, ANALYZE does not
    4. ANALYZE shows error messages for incorrect queries

    Explanation: ANALYZE actually runs the query for more accurate feedback, including times and number of rows processed. EXPLAIN is a planning tool and does not risk running slow or unsafe queries. Neither command is for table statistics collection or SQL error reporting.

12. Identifying Full Table Scans in EXPLAIN

    What does it mean if the EXPLAIN plan says 'Seq Scan' or 'Full Table Scan'?

    1. The query will only return unique rows
    2. The query will be aborted due to a syntax error
    3. A composite index will be used for each search
    4. The database will read every row in the table without using an index

    Explanation: A sequential or full table scan means each row is checked, indicating indexes are not used for that query step. Syntax errors stop the query before reaching EXPLAIN. Composite indexes are not used here. Uniqueness of returned rows is fixed by query logic, not the scan type.

13. When NOT to Use a Composite Index

    Which scenario makes a composite index less effective?

    1. Filtering only on columns not leading in the composite index
    2. Using the composite index columns in the WHERE clause
    3. Sorting results by the columns covered in the composite index
    4. Filtering on both columns defined in the composite index

    Explanation: If the query filters only by columns that are not the first in the composite index, the index can't be used efficiently. When both columns are filtered, or queries use the index columns for sorting or WHERE conditions, the composite index is beneficial.

14. Effect of Adding Columns to a Covering Index

    What is a possible downside of adding too many columns to a covering index?

    1. It ensures all queries will be executed as fast as possible
    2. It makes the database schema invalid
    3. It can make the index slower to update and require more storage space
    4. It will always speed up all types of queries

    Explanation: The more columns an index contains, the larger and slower it can become for writes or updates, and it will use more disk space. While some queries may benefit from the index, others could see a negative impact. The database schema remains valid regardless, and speed increases are not guaranteed universally.

15. Index-Only Scan in Query Plans

    What does an 'Index Only Scan' signify in a query execution plan?

    1. The query is using an index for only some columns
    2. There are duplicate rows in the index
    3. The database is checking for missing indexes
    4. All data needed can be retrieved from the index without reading the actual table rows

    Explanation: An 'Index Only Scan' means the query is covered by the index, so the main table rows don't need to be accessed, speeding up reads. It's not about partial index use or duplication. The plan does not identify missing indexes in this step.