When to Index a Table: A Practical Guide for Analysts

Indexes are a valuable tool for database performance, but they come with trade-offs. When a query is too slow, the first step is to examine the query execution plan using the EXPLAIN QUERY PLAN command. If the plan shows a "SCAN" against a large table, it means the database engine is reading every row to find the result. This is the worst-case scenario, and adding an index can help by allowing the engine to jump directly to the relevant data instead of scanning through the entire table.

An index is essentially a sorted copy of one or more columns from the table, kept alongside the original data. This sorted structure enables the database engine to quickly locate the desired rows by jumping to the specific index segment instead of scanning through every row. However, an index only benefits queries that filter or sort based on the indexed columns. If a query asks for unrelated columns or performs operations that don't match the index structure, the index will not provide any performance improvement.

The measurement of query performance on a 500,000-row orders table in SQLite showed that a simple scan took 90.9 milliseconds, while an index-based search took only 0.2 milliseconds. This significant difference demonstrates the potential speedup an index can bring to a query. In another example, filtering data by a specific date showed a full table scan took 123.5 milliseconds, whereas a covering index search took just 3.9 milliseconds, even though it had to return 42,470 rows.

The covering index allowed the engine to retrieve all necessary columns directly from the index without accessing the main table, resulting in a much faster response.

However, it's essential to consider the costs associated with indexes. Building an index takes time and resources, especially on large tables. Creating an index on 500,000 rows took 572 milliseconds, while a hundred million rows would take minutes. Additionally, the database file size increases with each added index. While indexes can substantially improve query performance, it's crucial to weigh these benefits against the time and disk space required to maintain them.