Indexing Strategies for Performance Optimization Quiz Quiz

Enhance your understanding of effective database indexing strategies with this quiz, designed to reinforce key performance optimization concepts. Explore best practices, common indexing pitfalls, and essential query optimization techniques for efficient data retrieval.

1. Primary Key Index Usage

   Why does having a primary key on a table automatically create an index in most relational databases?

   1. To randomly shuffle data order
   2. To compress data for storage savings
   3. To allow duplicate values in the table
   4. To uniquely identify rows and speed up lookups

   Explanation: A primary key uniquely identifies each row, so an index is created to guarantee fast access and enforce uniqueness. Allowing duplicates would defeat the purpose of a primary key, and compression is not a direct function of indexing. Indexing does not shuffle data randomly but organizes it for quick retrieval.

2. Choosing Columns for Indexing

   Which type of column is generally best suited for indexing to improve search query performance, as shown in a frequent name-search example?

   1. A column frequently used in WHERE clauses
   2. A column always holding the same value
   3. A column that rarely appears in queries
   4. A column containing only null values

   Explanation: Columns often used in WHERE conditions benefit the most from indexing, enabling faster search and retrieval. Columns rarely used or always null are not queried for filtering, making their indexing ineffective. Similarly, indexing a column with identical values offers no optimization since all results would be similar.

3. Unique vs. Non-Unique Indexes

   What is the main difference between a unique index and a non-unique index when applied to a column storing email addresses?

   1. A unique index increases storage needs more than a non-unique one
   2. A unique index enforces distinct values, while a non-unique index allows duplicates
   3. A unique index sorts data in ascending order by default
   4. A unique index speeds up deletion, while a non-unique index does not

   Explanation: A unique index guarantees that each value in the indexed column is different, which is often required for email addresses. Neither unique nor non-unique indexes directly affect data sorting or specifically impact deletion speeds, and their storage costs are generally similar except for the uniqueness constraint.

4. Composite Index Advantage

   How can a composite index on (first_name, last_name) benefit queries that search for both fields together?

   1. It prevents updates to the indexed columns
   2. It enables faster searches for combinations of both fields
   3. It doubles the size of the database
   4. It only works if names are misspelled

   Explanation: A composite index allows efficient searches when both fields are used in queries, such as looking up a specific full name. It does not handle misspellings, nor does it restrict updates to the indexed columns. While there is some storage overhead, it does not double the database size.

5. Over-Indexing Drawback

   What is a common drawback of adding too many indexes to a single table containing millions of rows?

   1. It reduces query result accuracy
   2. It limits the number of rows in the table
   3. It can slow down data modifications like inserts and updates
   4. It blocks read operations entirely

   Explanation: Excessive indexes require maintenance on each data change, increasing the time to insert or update records. Indexes do not influence query accuracy or impose direct row count limits. They also do not prevent read operations; rather, they typically accelerate search queries.

6. Indexing for Range Queries

   When frequently running queries like SELECT * FROM sales WHERE date BETWEEN '2024-01-01' AND '2024-03-31', which indexing strategy is most effective?

   1. Remove all indexes to minimize storage
   2. Create a unique index on the price column
   3. Create an index on the date column
   4. Index every column in the table

   Explanation: Range queries on the date column benefit greatly from an index, which allows quick location of records within the given dates. Indexing all columns is unnecessary and can negatively impact performance. Removing indexes makes searches slower, and indexing price with uniqueness does not directly help date range queries.

7. Dropping Unused Indexes

   Why should unused indexes be periodically dropped from a large database table?

   1. To merge duplicate rows automatically
   2. To save storage space and improve write performance
   3. To disable SELECT queries
   4. To alphabetize all entries

   Explanation: Unused indexes consume disk space and impose overhead during insert, update, or delete operations. They do not prevent SELECT queries and have no effect on automatically merging rows or changing the order of stored data. Keeping unnecessary indexes can therefore hinder overall performance.

8. Index Maintenance

   What routine database activity helps ensure that indexes remain efficient over time, especially in tables with lots of updates?

   1. Deleting all indexes after use
   2. Rebuilding or reorganizing indexes
   3. Adding random data to the indices
   4. Renaming indexed columns frequently

   Explanation: Rebuilding or reorganizing helps eliminate fragmentation and keeps index access paths efficient. Deleting indexes removes their optimization benefit. Frequently renaming columns or adding random data does not maintain or improve index effectiveness, and can disrupt operations.

9. Index Use in Sorting

   How can an index improve the speed of sorting data when running a query like SELECT * FROM orders ORDER BY order_date?

   1. By randomly skipping some rows
   2. By allowing the database to quickly access rows in sorted order
   3. By encrypting data for security
   4. By preventing duplicate records in the table

   Explanation: Indexes on the sorting column help access data in the desired order, reducing sorting effort. They do not prevent duplication, encrypt the information, or randomly skip rows, all of which do not relate directly to sorting efficiency.

10. Partial Indexing

    In a table where most rows have a status of 'active', how can a partial index on status='inactive' help optimize specific queries?

    1. It removes all 'active' rows from the table
    2. It focuses the index only on a subset of rows, making queries for 'inactive' faster
    3. It blocks queries on 'active' records
    4. It sorts results by row length

    Explanation: Partial indexes are useful when only a fraction of rows should be searchable quickly, such as those marked 'inactive'. They do not remove rows, sort results by row length, or block access to other data. Instead, they target the indexing to specific conditions, optimizing relevant queries.