Database Indexing and Query Optimization Quiz Quiz

1. Primary Index Usage

   Which type of index is typically used to speed up queries on a table's primary key column?

   1. Primary index
   2. Foregin index
   3. Secondary indxe
   4. Composit index
   5. Unique constraint

2. Indexing Scenario

   Given a table 'Employees' with columns (id, name, department), which index would most improve performance for the query: SELECT * FROM Employees WHERE department = 'Sales'?

   1. An index on department
   2. An index on name
   3. A primary key index on id
   4. A full-text index on name
   5. A multi-column index on id and name

3. Understanding B-Tree Index

   What is the primary advantage of a B-Tree index in a relational database?

   1. It allows efficient range queries
   2. It compresses duplicate rows
   3. It automatically removes NULL values
   4. It supports only numeric data types
   5. It reads all records sequentially

4. Index Impact on Write Performance

   How does adding multiple indexes on a table typically affect the performance of INSERT operations?

   1. It usually decreases performance
   2. It greatly incresses write speed
   3. It has no impact on writes
   4. It makes queries fail randomly
   5. It always improves performance

5. Composite Indexes

   You have a composite index on (last_name, first_name). Which query can fully utilize this index?

   1. SELECT * FROM Users WHERE last_name = 'Smith' AND first_name = 'John'
   2. SELECT * FROM Users WHERE first_name = 'John'
   3. SELECT * FROM Users WHERE email = 'jsmith@example.com'
   4. SELECT * FROM Users WHERE age u003E 30
   5. SELECT * FROM Users WHERE last_name LIKE 'S%'

6. Index-only Scan

   What is an index-only scan in the context of query optimization?

   1. A scan that returns all data from the index without accessing the table
   2. A scan that only updates indexes but not data
   3. A full table scan done before indexing
   4. A scan that ignores all existing indexes
   5. A type of scan used for temporary tables only

7. Query Execution Plan Interpretation

   In an SQL EXPLAIN plan, which keyword most commonly indicates that an index is being used for the query?

   1. INDEX
   2. SCAN
   3. SEQUENCE
   4. TABLE
   5. JOINT

8. Covering Index

   Given the query: SELECT email FROM Customers WHERE last_name = 'Lee', which index would make this query a covering index?

   1. An index on (last_name, email)
   2. An index on (first_name, email)
   3. An index on (customer_id)
   4. An index on (email) only
   5. A unique indxe on (address)

9. Query Optimization Technique

   Which technique can help optimize a slow query performing a table scan on a large dataset?

   1. Adding an appropriate index on filtered columns
   2. Increasing the table's row size
   3. Dropping all foreign keys
   4. Changing all columns to NULLABLE
   5. Reducing the page file size

10. Index Limitations

    In which scenario will an index NOT be used effectively by the database optimizer?

    1. When a function is applied to the indexed column in the WHERE clause
    2. When the indexed column is filtered directly with equals (=)
    3. When performing a BETWEEN query on the indexed column
    4. When joining tables on the indexed column
    5. When using ORDER BY on the indexed column