Database Indexing Basics and Performance Quiz Quiz

Test your understanding of database indexing principles such as B-tree structures, composite and partial indexes, covering indexes, and performance analysis using EXPLAIN and ANALYZE statements. This quiz reinforces essential indexing concepts to help improve query efficiency and database performance.

1. B-tree Index Functionality

   Which of the following best describes the primary purpose of a B-tree index in a relational database?

   1. To store binary large objects efficiently
   2. To maintain database transactions automatically
   3. To reduce data redundancy
   4. To speed up searches by providing sorted access to rows

   Explanation: B-tree indexes organize data in a balanced tree structure, significantly speeding up search operations by efficiently locating records. While data redundancy, transaction management, and blob storage are important issues in databases, they are not directly related to the main purpose of B-tree indexes. Options about redundancy, blobs, or transactions do not accurately reflect the key use of B-trees.

2. Composite Index Definition

   What is a composite index in the context of relational databases?

   1. A temporary in-memory structure
   2. A backup of the primary key
   3. An index combining values from multiple columns
   4. An index created on a single column

   Explanation: A composite index is built using more than one column, which helps queries filter by multiple columns more quickly. An index on a single column is not composite, while backups and temporary in-memory structures are unrelated distractors. The correct definition highlights the use of multiple columns, which sets composite indexes apart.

3. Partial Index Use Case

   Which scenario best illustrates when to use a partial index?

   1. You must store duplicate indexes for backup
   2. You want to index only active user accounts for faster lookups
   3. You need to index every row in a table
   4. You want to encrypt column data

   Explanation: Partial indexes are useful when you only need to index a subset of rows, such as active users, rather than all records. Indexing the entire table would not be partial, and backups or encryption are unrelated features. Using partial indexes optimizes storage and improves performance for targeted queries.

4. Understanding Covering Indexes

   What is a covering index in SQL terminology?

   1. An index implemented using hash maps
   2. An index that covers only foreign key columns
   3. An index used only for backup and recovery
   4. An index that includes all columns required by a query

   Explanation: A covering index stores all columns needed for a query so the database can satisfy the request using only the index, improving efficiency. Indexes for foreign keys or hash maps are distinct concepts, and backup uses are unrelated. The essence is that the index 'covers' the query without accessing the main table.

5. EXPLAIN Keyword Purpose

   What does the EXPLAIN statement help database users achieve?

   1. Display how a query will be executed
   2. Create a new database schema
   3. Encrypt columns for security
   4. Delete duplicate rows

   Explanation: EXPLAIN provides insight into how a database will execute a query, revealing indexing and access methods. Encryption, deletion, and schema creation are unrelated actions not associated with the EXPLAIN statement. The true value is in visualizing the query plan to optimize performance.

6. ANALYZE Statement Purpose

   Which best describes the use of the ANALYZE statement in a relational database?

   1. To collect and update statistics about table contents
   2. To delete all indexes
   3. To truncate tables
   4. To sort the results of queries

   Explanation: ANALYZE helps maintain up-to-date statistics on table data, which assists the query planner in making efficient decisions. Deleting indexes, truncating tables, and sorting results are different operations not associated with this command. Accurate statistics are important for effective indexing and query performance.

7. Index Scan vs. Sequential Scan

   When a database uses an index scan instead of a sequential scan, what benefit does it provide?

   1. Data is always automatically encrypted
   2. All columns are compressed by default
   3. It guarantees no duplicate rows
   4. It can locate relevant rows more quickly

   Explanation: An index scan can quickly find specific rows based on index keys, improving performance compared to scanning every row. Index scans do not provide encryption, compression, or deduplication features, which are handled differently. The main advantage is speeding up lookups.

8. Choosing Index Columns

   Which factor is most important when deciding which columns to include in an index?

   1. Columns used only in SELECT lists
   2. Columns never referenced by any query
   3. Columns frequently used in WHERE clauses
   4. Columns containing only null values

   Explanation: Indexing columns commonly used in WHERE clauses can significantly speed up filtering in queries. SELECT-list-only columns or those with only nulls or no references benefit little from indexing. Relevant columns should match query patterns to maximize index effectiveness.

9. Index Storage Overhead

   What is a potential drawback of creating too many indexes on a table?

   1. It can slow down insert and update operations
   2. It disables transaction logging
   3. It forces all tables to be read-only
   4. It always increases query speed

   Explanation: Maintaining many indexes increases overhead during data changes, slowing inserts and updates. More indexes do not necessarily boost speed, nor do they affect logging or read-only status. Balancing the number of indexes is key for performance.

10. Unique Index Purpose

    What main function does a unique index serve in a database?

    1. It automatically runs backup scripts
    2. It compresses numerical columns
    3. It prevents duplicate values in the indexed columns
    4. It sorts tables alphabetically

    Explanation: Unique indexes enforce the rule that the indexed columns cannot have duplicate values. Sorting, compression, or backup features are not functions of unique indexes. The enforcement of uniqueness helps maintain data integrity.

11. Covered Query Example

    Suppose a query requests columns A and B, and there is an index on (A, B). What is this scenario called?

    1. A partial index is created
    2. A full table scan is required
    3. A primary key lookup is required
    4. A covering index is used

    Explanation: With both requested columns in the index, the database can satisfy the query using just the index—a scenario known as a covering index. Full table scan, primary key lookup, and partial index do not accurately describe this efficient access method.

12. EXPLAIN Output: Index Usage

    When running EXPLAIN on a query, what indicates that an index is being used?

    1. The output shows 'Index Scan' or similar term
    2. The output lists all SQL code
    3. The output only displays column data types
    4. The output includes the database version

    Explanation: Seeing 'Index Scan' or similar in EXPLAIN results means the query planner chooses to use an index. Database version, code listing, or column data types in the EXPLAIN output are not direct indications of index usage.

13. Partial Index Benefit

    What is the main advantage of a partial index over a regular index?

    1. Automatically indexes all foreign key columns
    2. Covers more queries than a regular index
    3. Prevents all duplicate rows in the table
    4. Requires less storage space and speeds up queries for specific data subsets

    Explanation: Partial indexes cover only part of a table, reducing space and improving performance for targeted queries. They do not necessarily cover more queries, auto-index foreign keys, or prevent all duplicates. Their main strength is focusing on relevant data.

14. Index Order and Composite Indexes

    If you have a composite index on (customer_id, order_date), which query will benefit most from this index?

    1. A query filtering by order_date alone
    2. A query filtering by customer_id and order_date
    3. A query filtering by order_status only
    4. A query filtering only by a product_name column

    Explanation: Composite indexes are most effective when queries filter by the indexed columns in order, so using both customer_id and order_date is optimal. Filtering only by non-indexed columns or just the second part (order_date) reduces index efficiency.

15. EXPLAIN vs. ANALYZE Output

    What is the primary difference between EXPLAIN and EXPLAIN ANALYZE statements?

    1. EXPLAIN ANALYZE formats the output in XML
    2. EXPLAIN can only be used for SELECT statements
    3. EXPLAIN shows estimated query plans, while EXPLAIN ANALYZE shows actual execution details
    4. Both commands always produce identical output

    Explanation: EXPLAIN offers a prediction of the query plan, while EXPLAIN ANALYZE executes the query and reports real timing and row counts. They do not produce the same output, ANALYZE is not mainly about output format, and both can be used with several types of statements.

16. Updating Data with Indexes

    How do indexes typically affect UPDATE statements on a table?

    1. Indexes only affect SELECT queries
    2. Indexes automatically encrypt the updated values
    3. Indexes can increase the time required to complete UPDATEs
    4. Indexes always block all UPDATEs

    Explanation: Having indexes means that updates must also modify the index entries, potentially slowing down UPDATEs. Indexes do not block updates, encrypt values, or exclusively affect SELECTs. The maintenance of index structures upon updates is the main impact.