Optimizing Indexing for Time-Series Data Quiz

This quiz tests your understanding of indexing strategies and best practices for efficient time-series workloads, including range scans on timestamps, composite and covering indexes, and query optimization techniques.

1. Purpose of Indexing on Timestamp Columns

   Why is creating an index on the timestamp column important for time-series databases when querying recent records?

   1. It speeds up range scans for queries filtering on time ranges.
   2. It encrypts the timestamp column automatically.
   3. It forces records to be always in memory.
   4. It increases storage space for timestamp values.
   5. It combines all columns into a single field.

2. Identifying Full Scans with EXPLAIN

   When using EXPLAIN to analyze a query that filters on timestamp, which keyword indicates that a full table scan is occurring?

   1. ALL
   2. HASH
   3. JOIN
   4. USING INDEX
   5. SPLIT

3. Composite Index Usage

   If a table has both a 'timestamp' and a 'sensor_id' column, which composite index would best optimize queries filtering on both columns?

   1. INDEX(timestamp, sensor_id)
   2. INDEX(value)
   3. INDEX(sensor_id)
   4. INDEX(sensor_id, value)
   5. INDEX(value, timestamp)

4. Understanding Covering Indexes

   Which situation benefits the most from a covering index in a time-series workload?

   1. When deleting all records by a certain timestamp.
   2. When aggregating values across unrelated columns.
   3. When updating all records in a table regularly.
   4. When sorting text fields alphabetically.
   5. When a query retrieves only columns present in the index definition.

5. Effectiveness of Partial Indexes

   What is a key advantage of defining a partial index on a timestamp column for recent data only?

   1. It disables queries for old records.
   2. It forces all queries to use primary keys.
   3. It reduces index size and improves performance for recent data queries.
   4. It removes duplicates automatically.
   5. It increases the number of joins.

6. Inefficient Index Design Example

   Which index design is likely to be less effective for a query filtering primarily on timestamp and status?

   1. INDEX(timestamp)
   2. INDEX(timestamp, status)
   3. INDEX(timestamp, value)
   4. INDEX(status, timestamp)
   5. INDEX(timestamp, status, value)

7. Detecting Table Scans in EXPLAIN Output

   In the output of EXPLAIN, which phrase suggests that the database is using an index to satisfy all columns needed by the query?

   1. INDEX MISS
   2. USING INDEX
   3. FULL SCAN
   4. HASH TABLE
   5. TABLE LOCK

8. Impact of Missing Indexes

   What is the most likely impact if a time-series query lacks an index on the timestamp column?

   1. The timestamp format will change.
   2. Unique constraints will be enforced.
   3. The query will return incorrect results.
   4. The query will be slower due to full table scans.
   5. The database will crash.

9. Purpose of Using EXPLAIN

   Why is the EXPLAIN command useful when optimizing queries for time-series workloads?

   1. It converts date formats automatically.
   2. It shows how the database plans to access data and use indexes.
   3. It reorders the data physically on disk.
   4. It drops unused columns from results.
   5. It generates random sample results.

10. Order of Columns in a Composite Index

    When creating a composite index for queries using both timestamp and device_id as filters, why should timestamp generally come first in the index definition?

    1. Because device_id columns are always unique.
    2. Because it is not possible to index device_id.
    3. Because composite indexes cannot start with device_id.
    4. Because queries typically filter on timestamp ranges, helping index efficiency.
    5. Because timestamp columns require less storage.