In this lesson, you will learn how to design composite indices to minimize disk I/O—the primary performance bottleneck in embedded systems.
Reducing Disk Lookups with Composite Indices
In embedded environments like IoT sensors or mobile devices, storage access is expensive and often slow compared to CPU cycles. When a query requires multiple filters, a single-column index is rarely enough. If your query filters by sensor_id and timestamp, but you only have an index on sensor_id, the database engine must fetch the rows from the disk and then manually check the timestamp for each result. This leads to excessive "page" reads, which drains power and increases latency.
A composite index (or multi-column index) acts as a shortcut. By grouping columns that are frequently used together, the database can navigate the B-Tree structure to find the exact range of data without needing to perform extra lookups.
Designing for Efficiency
Consider a scenario where you are monitoring temperature data. Your most common query is:
SELECT temp FROM readings WHERE sensor_id = 5 AND timestamp > 1625000000;
If you create an index like this:
CREATE INDEX idx_sensor_time ON readings (sensor_id, timestamp);
The database engine traverses the index, finds all records for sensor_id = 5, and then immediately narrows them down to the specific timestamp range. Because the index stores the values in a sorted order that matches your query, the database retrieves the required data in a single path through the B-Tree, effectively "covering" the query.
Strategic Ordering
The order of columns in your composite index matters significantly. Always place the column used for equality checks (like sensor_id = 5) first, and the column used for range scans (like timestamp > X) second. This allows the engine to jump directly to the correct starting point in the index and perform a linear scan only on the relevant subset of records. By aligning your index structure with your most frequent query patterns, you significantly reduce the number of disk pages that need to be loaded into the cache.
Key Takeaway: Design composite indices by placing equality-checked columns before range-checked columns to ensure the database can resolve your most common queries with the fewest possible disk page reads.