Essential Database Indexing Basics Quiz Quiz

Test your understanding of fundamental database indexing concepts, including composite and covering indexes, and how to interpret EXPLAIN and QUERY PLAN outputs. This quiz is designed to help you review key principles for optimizing database queries and improving performance.

1. Composite Index Components

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

   1. An index created using only the primary key
   2. An index that stores duplicate data
   3. An index that includes only one column
   4. An index created using multiple columns

   Explanation: A composite index involves more than one column to optimize searches that require filtering or ordering by several fields. An index created using only the primary key is not composite, as it contains just one column. Storing duplicate data is not a defining feature of composite indexes. An index that includes only one column is a single-column index, not composite.

2. Covering Index Benefits

   What is a main benefit of a covering index for certain database queries?

   1. It increases storage usage significantly for all queries
   2. It always improves updates and deletes
   3. It allows queries to be answered using only the index, avoiding table access
   4. It slows down SELECT statements

   Explanation: A covering index contains all the columns needed for a query, so the database can fulfill the request without accessing the main table, improving speed. Covering indexes do not always improve updates and deletes, which can become slower. While they may increase storage usage, this is not universal or their main feature. Far from slowing SELECT statements, covering indexes are designed to make them faster.

3. EXPLAIN Plan Purpose

   Which statement best describes the purpose of the EXPLAIN or QUERY PLAN command in databases?

   1. It encrypts sensitive table columns
   2. It automatically creates indexes for faster queries
   3. It changes the way data is stored in tables
   4. It shows how a query will be executed by the database engine

   Explanation: EXPLAIN plans reveal the steps a database engine will take to execute a query, so users can analyze and optimize performance. It does not alter data storage or modify table records. The command does not automatically create indexes or perform encryption, both of which require separate actions.

4. Reading Index Usage in Query Plans

   If an EXPLAIN plan indicates 'Index Scan' for a particular query, what does this mean?

   1. The query accesses data using an index rather than scanning the whole table
   2. An error occurred during query execution
   3. The query is ignored by the engine
   4. The database skipped using any indexes

   Explanation: An 'Index Scan' means the database is leveraging an existing index to search for data, which is more efficient than scanning the entire table. If the plan said 'Seq Scan' instead, it would indicate no index use. An 'Index Scan' does not mean an error or that the query is ignored—it is actually a sign of performance optimization.

5. ORDER BY and Indexes

   When can a database use an index to optimize a query with an ORDER BY clause?

   1. When the ORDER BY uses columns not present in any index at all
   2. When the columns are listed in reverse order in the index
   3. When the columns in ORDER BY match the leading columns of an available index
   4. When the query has no WHERE clause

   Explanation: Indexes can help ORDER BY performance if the ordering columns align with the first (leading) columns of an index. If the columns are not present in any index, or listed in reverse, the index cannot be fully utilized for sorting. The presence or absence of a WHERE clause does not affect whether the ORDER BY can use an index.

6. Composite Index Column Order

   In what situation might the order of columns in a composite index matter for query optimization?

   1. When there is no WHERE clause in any query
   2. When filtering or sorting queries use only the initial columns of the index
   3. When the index is not referenced in queries
   4. When all columns are always used together in every query

   Explanation: The order of columns in a composite index is important because the database can quickly use the index only when the first columns are included in search or sort conditions. If no WHERE clause is present, indexing is usually less helpful. If all columns are always used, order is less significant, but partial matches are common. An unused index provides no optimization regardless of column order.

7. Index and Table Size

   How can creating an index on a large table affect query performance?

   1. It reduces the number of columns in the table
   2. It always doubles the size of the table data
   3. It can significantly speed up search queries involving indexed columns
   4. It has no effect on query performance

   Explanation: Indexes are designed to make data retrieval on indexed columns much faster, which is especially valuable in large tables. They do not double the data size, as the index typically only references key data, not the entire row. Indexes do not reduce columns in a table. Claiming no effect is incorrect, as indexes do impact performance.

8. Detecting Missing Indexes

   Suppose a SELECT query consistently performs a full table scan and is slow; what might this indicate?

   1. The query is not allowed to use any indexes
   2. An index is always created automatically for every column
   3. A suitable index may be missing for the columns used in the query
   4. The query must always be slow by design

   Explanation: Slow full table scans for queries that filter or sort could mean there is no appropriate index on the relevant columns. Slow performance is not always by design and can often be improved. Databases do not prohibit indexes nor automatically create one for every column, as this would waste resources.

9. Unique Index Function

   What is a primary characteristic of a unique index in a database?

   1. It causes duplicate rows to appear in query results
   2. It ensures that indexed column values are not duplicated
   3. It prevents any search operation on the indexed columns
   4. It stores no information about the columns

   Explanation: A unique index enforces uniqueness, meaning each value in the indexed columns must be distinct. It definitely stores information about the columns it covers. Rather than causing duplicates, unique indexes are designed to prevent them. They also make searching possible, not impossible.

10. Impact of Too Many Indexes

    What is a possible downside of creating too many indexes on a single database table?

    1. It causes all data to be deleted automatically
    2. It can slow down INSERT, UPDATE, and DELETE operations
    3. It will always speed up every type of query
    4. It makes the table read-only

    Explanation: While indexes speed up searches, having many of them can slow down INSERT, UPDATE, and DELETE operations because the database must update each index entry on data changes. It is untrue that indexes always speed up every query; some are unaffected or even slowed down. Indexes neither make tables read-only nor cause automatic data deletion.