Database Indexing Essentials: Composite, Covering, and EXPLAIN Quiz

Test your knowledge on database indexing basics—covering concepts like composite and covering indexes, the use of EXPLAIN, and the balance between read and write performance. Ideal for learners seeking to strengthen their understanding of how indexing impacts database queries and efficiency.

1. Composite Index Identification

   Which option best describes a composite index in a database?

   1. An index that prevents duplicate rows.
   2. An automatically generated index on every table.
   3. An index created on two or more columns together.
   4. An index that only includes the primary key column.

   Explanation: Composite indexes are built on multiple columns, allowing efficient lookups when queries filter on those columns together. They are not limited to just the primary key or automatically generated for all tables. Preventing duplicate rows is the job of unique constraints, not standard composite indexes.

2. Covering Index Concept

   In the context of indexing, what is a covering index?

   1. An index that includes all columns needed by a query.
   2. An index that covers multiple tables at once.
   3. An index that increases write performance.
   4. An index used only for sorting rows.

   Explanation: A covering index contains all fields required to satisfy a particular query, so the database engine can return results from the index itself. It does not span multiple tables, is not solely for sorting, and typically speeds up reads but may not improve writes.

3. Purpose of the EXPLAIN Statement

   What is the main purpose of using the EXPLAIN statement before a query?

   1. To create a new index automatically.
   2. To rename columns in the result.
   3. To understand how the database plans to execute the query.
   4. To update data in the table.

   Explanation: The EXPLAIN statement shows the query execution plan and reveals whether indexes will be used, helping to optimize queries. It does not rename columns, modify data, or create new indexes.

4. Index and Write Performance

   How do indexes generally affect the performance of write operations such as INSERT, UPDATE, or DELETE?

   1. They have no effect on writes.
   2. They make writes impossible.
   3. They typically slow down write operations slightly.
   4. They always make writes much faster.

   Explanation: Indexes must be updated when data changes, so they usually introduce some overhead to write operations. They do not make writes faster, have no effect, or prevent them entirely.

5. Optimal Index for Sorting

   Which index type would best improve the speed of a query sorting rows by two columns, for example, ORDER BY last_name, first_name?

   1. A composite index on (last_name, first_name)
   2. A primary key index not including these columns
   3. A full-text index on both columns
   4. A single-column index on first_name

   Explanation: A composite index that matches the ORDER BY columns allows the database to efficiently sort and retrieve data. A single-column index or an index on unrelated columns will not fully optimize such a sort, and full-text indexes are for searching text, not sorting.

6. Index and Read Performance

   When a query can use an appropriate index, how does this generally affect read operation speed?

   1. It always makes queries slower.
   2. It can improve query speed significantly.
   3. It prevents the database from returning results.
   4. It only affects the display on the screen.

   Explanation: Proper indexing can drastically reduce the amount of data scanned, speeding up queries. Indexes generally do not slow down reads, prevent results, or control the display output.

7. Single vs Composite Indexes

   If you have separate single-column indexes on 'username' and 'email', which type of query may not be fully optimized?

   1. A query sorting by a column not indexed.
   2. A query filtering by both 'username' and 'email' together.
   3. A query selecting 'email' only.
   4. A query selecting 'username' only.

   Explanation: Single-column indexes are effective for queries filtering on one column, but a composite index is better for queries filtering on both 'username' and 'email' at the same time. Filtering by just one column will still use the corresponding single-column index, and sorting by an unindexed column is unrelated.

8. When to Avoid Indexes

   In which situation is it usually best to avoid adding an index on a column?

   1. When the column is rarely used in WHERE clauses.
   2. When fast searching is important.
   3. When a column's values are almost always unique.
   4. When there are frequent reporting queries on that column.

   Explanation: Indexing columns not used in search conditions wastes resources without speeding up queries. High uniqueness or frequent usage in queries can benefit from an index, and indexing is advisable for columns regularly queried for fast lookups.

9. Interpreting EXPLAIN Output

   If EXPLAIN output for a query shows 'Full Table Scan', what does this mean?

   1. The database checks every row without using an index.
   2. The database looks up a single row directly.
   3. The query has a syntax error.
   4. The database uses only partial indexes.

   Explanation: A full table scan means no suitable index was used, so each row is checked individually. This is slower than direct index usage. Partial index use or syntax errors are unrelated, and direct lookups suggest indexed queries.

10. Column Order in Composite Indexes

    Why does the order of columns in a composite index matter for query optimization?

    1. Because only the first column is ever used.
    2. Because indexes are unordered.
    3. Because columns must be alphabetical.
    4. Because only the leading column(s) are directly usable for range scans.

    Explanation: The order affects which columns can be used efficiently in searches; only the leading columns support fast searches or sorts. Indexes are always ordered, not random, and all included columns can be utilized if queries match the order. There is no requirement for alphabetical order.

11. Index Trade-Offs

    What is a typical trade-off when adding more indexes to a table?

    1. Faster reads, but potentially slower writes.
    2. Faster writes but slower reads.
    3. No impact on performance.
    4. Slower queries and slower writes.

    Explanation: While more indexes improve query speed, they add overhead to data changes because all relevant indexes must update. Adding indexes does not slow both reads and writes, nor does it speed up writes at the expense of reads. There is always some performance effect.

12. Dropping Indexes Effect

    What is a likely outcome if an unused index on a large table is dropped?

    1. A new primary key will be created.
    2. The table will become corrupted.
    3. Write operations may become faster.
    4. Queries using that index will be much faster.

    Explanation: Removing an unused index can speed up inserts, updates, and deletes since there’s one less data structure to update. Dropping an index doesn't speed up queries depending on it, cause corruption, or create a new primary key, which is a separate table property.

13. Partial vs Full Index Coverage

    Given a query 'SELECT age FROM users WHERE last_name = ? AND first_name = ?', how can a covering index be achieved?

    1. Create an index on (last_name, first_name, age)
    2. Create a unique constraint on last_name
    3. Add a foreign key to the table
    4. Create an index on (age) only

    Explanation: Including all columns referenced in SELECT and WHERE clauses within one index lets the database resolve the query with just the index. Indexing only 'age' is ineffective because it does not cover the WHERE clauses. Unique constraints and foreign keys serve different database integrity purposes.

14. Duplicate Indexes

    What is the effect of having two identical indexes on the same columns in a table?

    1. It enforces uniqueness twice.
    2. It wastes space and can slow down writes.
    3. It causes the database to ignore both indexes.
    4. It improves query performance even more.

    Explanation: Duplicate indexes add unnecessary storage and maintenance cost, with no added performance benefit. Having identical indexes doesn't increase speed, enforce uniqueness, or cause the database to disable them.

15. Index Use in Simple Scenarios

    Which type of query benefits most from a single-column index on 'customer_id'?

    1. SELECT * FROM orders WHERE customer_id = 123
    2. SELECT * FROM orders WHERE amount u003E 1000
    3. SELECT * FROM orders ORDER BY date DESC
    4. SELECT * FROM orders

    Explanation: A query filtering by 'customer_id' matches the index, speeding lookups for that value. Filtering by another column, ordering by date, or fetching all rows without filters does not use the single-column 'customer_id' index efficiently.

16. Indexing for Range Queries

    Which index would speed up a query like 'SELECT * FROM sales WHERE date u003E= ? AND date u003C= ?'?

    1. A composite index on 'price' and 'quantity'
    2. A unique index on 'customer_id'
    3. A single-column index on 'date'
    4. A primary key on 'order_id'

    Explanation: A single-column index on the 'date' field optimizes searches bounded by date ranges. Composite indexes on unrelated columns, unique indexes on different fields, or primary keys unrelated to 'date' will not help for this type of range query.