English is currently available.
SQL DISCUSSION
My measurements table has about 5 million rows and an index on created_at. A query with WHERE YEAR(created_at) = 2025 AND device_id = 17 still takes several seconds, and the execution plan shows a full table scan.
I would like to understand what an index really is internally, why the database ignores mine here, and how to decide which columns to index.
Most indexes are B-trees: a sorted copy of the indexed column values, each with a pointer to its table row, arranged in pages that form a shallow tree. Every page holds a few hundred keys, so the tree fans out very quickly. With around 300 keys per page, three levels can address 300 × 300 × 300 = 27 million entries, so finding one value among 5 million rows means reading 3 or 4 pages instead of scanning every row.
Because the entries are sorted, the same structure serves equality lookups, range conditions such as BETWEEN, and ORDER BY on that column.
Your index is ignored because of YEAR(created_at). The index is sorted by the stored value of created_at, not by the result of a function applied to it, so the database would have to compute the function for every row. Rewrite the condition as a range on the bare column: created_at >= '2025-01-01' AND created_at < '2026-01-01'.
The same problem appears with arithmetic on the column, with implicit type conversion (comparing a text column with a number), and with LIKE '%abc'. A leading wildcard cannot use the sort order, while LIKE 'abc%' can.
For this query a composite index on (device_id, created_at) is better than two single-column indexes. The entries are sorted by device first and by time within each device, so the database jumps to device 17 and reads one contiguous slice for the year.
Column order matters. An index on (A, B) helps conditions on A alone or on A and B, but generally not on B alone, just as a phone book sorted by surname then first name is little help when you only know the first name. Put the equality columns first and the range column last.
Even a usable index is skipped when it would not save work. Following an index entry to the table row is a separate read for each row, so if the condition matches a large share of the table, scanning it sequentially is cheaper and the optimizer chooses that. An index on a column with only a few distinct values, such as a status flag, is rarely useful by itself for that reason.
The optimizer decides from table statistics, so if plans look wrong after a bulk load, refresh the statistics with your database's ANALYZE or UPDATE STATISTICS command.
Indexes are not free: every INSERT, DELETE and UPDATE of an indexed column has to maintain each index, and they take disk space and memory. Index the columns that appear in your frequent WHERE, JOIN and ORDER BY clauses rather than every column, and remember that foreign-key columns are not indexed automatically in every database.
Verify each change with the plan: EXPLAIN in MySQL and PostgreSQL, or the execution plan view in SQL Server. You want to see an index seek or range scan on the new index and a row estimate close to the real count.
