Database Indexing basics Quiz

Test your understanding of database indexing fundamentals with this quiz covering composite keys, covering indexes, and interpreting EXPLAIN output. Ideal for learners seeking to solidify basic concepts of database optimization and query performance.

1. Composite Key Identification

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

   1. A single column that acts as a primary key
   2. A set of two or more columns used together as a primary key
   3. An automatically incremented column
   4. A key that contains only numeric data

2. Basic Index Purpose

   What is the main purpose of creating an index on a database column?

   1. To allow more users to access the database simultaneously
   2. To increase the size of the database
   3. To encrypt the data in a column
   4. To speed up query performance for data retrieval

3. EXPLAIN Statement Usage

   What does the EXPLAIN keyword do when placed before a SELECT statement in SQL?

   1. It runs the query and shows the result
   2. It deletes any existing indexes used in the query
   3. It explains the syntax of the query
   4. It provides a breakdown of how the database will execute the query

4. Composite Index Order Impact

   Given a composite index on columns (first_name, last_name), which query benefits most from this index?

   1. SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Smith'
   2. SELECT * FROM users WHERE last_name = 'Smith'
   3. SELECT * FROM users WHERE city = 'Boston'
   4. SELECT * FROM users WHERE age = 30

5. Covering Index Recognition

   Which of the following describes a 'covering index'?

   1. An index that automatically updates deleted rows
   2. An index that is hidden from the query optimizer
   3. An index that covers only the primary key column
   4. An index containing all the columns needed by a query

6. Index Lookup Type

   If the output of EXPLAIN shows 'index' in the type column, what does this typically indicate?

   1. The query is performing a full table scan
   2. The query is updating indexes
   3. No indexes are available
   4. An index is being used to read all the data needed for the query

7. Index Benefit Example

   Which scenario would benefit MOST from adding an index on a 'created_at' timestamp column?

   1. Inserting new rows into the table
   2. Deleting old data using random IDs
   3. Frequently searching for rows based on the creation date
   4. Calculating the sum of all numeric columns

8. Composite Key Use Case

   Why might a table use a composite key made up of (student_id, course_id)?

   1. To uniquely identify the enrollment of each student in each course
   2. To allow duplicate rows for the same student in the same course
   3. To store grades in a separate column
   4. To limit the number of courses a student can take

9. EXPLAIN Output Columns

   When viewing the output of EXPLAIN, which column typically shows which index (if any) the database plans to use?

   1. owner
   2. rows
   3. select_type
   4. key

10. Covering Index Performance

    How does a covering index help improve query performance?

    1. It slows down all SELECT statements
    2. It encrypts all indexed columns for security
    3. It allows the query to avoid accessing the main table since all required columns are in the index
    4. It increases the number of columns that can be queried