When data.table::setkey Earns Its Keep: Repeated Filters, Joins, and Grouped Summaries

A common shape of R code: a large in-memory data.table, a vector of IDs, and a loop that filters the table once per ID. Each filter scans the whole table, so the work grows with the number of lookups. If the same columns are filtered, joined, or grouped many times, setkey() can replace those repeated scans with one sort and cheaper lookups afterward.

The thesis: keying pays off when the same key columns are reused across many operations. It is a poor fit for a one-off filter on a small table, and it changes row order, which matters.

What setkey actually changes

setkey(DT, id) sorts DT by id and marks that column as the table's key. It does this by reference: the object is modified in place rather than copied. Afterward, key(DT) returns the key columns, haskey(DT) returns TRUE, and tables(DT) reports the table as keyed.

data.table's general form is DT[i, j, by]: i selects rows, j computes columns, and by groups. A key changes how i and by are evaluated. Equality filters on a keyed column can use binary search on the sorted order instead of a full scan, and by on a key prefix can reuse the existing order instead of re-sorting.

Worked example: repeated lookups

The code below is a template, not a tested benchmark. Run it in a fresh R session and record packageVersion("data.table"), because join and indexing behavior has changed across releases. Timings depend on table size, key cardinality, hardware, and memory pressure.

library(data.table)
packageVersion("data.table")

set.seed(1)
n <- 5e6
DT <- data.table(
  id    = sample.int(1e5, n, replace = TRUE),
  grp   = sample(letters[1:5], n, replace = TRUE),
  value = rnorm(n)
)

ids <- sample(DT$id, 200)

# Unkeyed: each filter scans the table
system.time({
  for (i in ids) DT[id == i, sum(value)]
})

setkey(DT, id)
key(DT)      # "id"
tables(DT)   # reports keyed columns

# Keyed: lookups can use the sorted key
system.time({
  for (i in ids) DT[.(i), sum(value)]
})

DT[.(i)] is shorthand for a join on the key. data.table also optimizes DT[id == i] to a binary search when id is keyed, so both forms can benefit. The explicit .(i) form makes the intent clear.

Joins and grouped summaries reuse the same order

A keyed join can attach columns from a lookup table without a full merge:

lookup <- data.table(id = ids, label = paste0("id-", ids))
setkey(lookup, id)

DT[lookup, on = .(id), nomatch = 0]

Grouped aggregation can also reuse a key. If grp were the key, DT[, .(total = sum(value), n = .N), by = grp] can group in key order and return sorted groups. If grp is not keyed, data.table still computes the same result, but it has to build the groups itself. For programmatic column names, setkeyv(DT, c("grp", "id")) sets a multi-column key.

Trade-offs and limits

• Row order changes. setkey sorts the table. Code that depends on the original order should add an order column before keying, or work on a copy.
• Side effects. Because keying is by reference, a function that calls setkey on its argument can change the caller's object. Use copy(DT) if that is not intended.
• Floating-point keys. Exact equality on doubles can be surprising. Prefer integer, character, factor, or Date keys, or round or scale explicitly.
• Cardinality and reuse. A key on a high-cardinality column helps only if the same column is queried many times. A single lookup on a small table will not justify the sort.
• Not a database index. This is an in-memory R optimization. It does not persist across sessions and does not replace database indexing.
• Secondary indexes. If row order must be preserved, setindex(DT, id) can speed up some equality filters and joins without reordering, though the set of optimized operations is narrower than with a key.

How to check the result

1. Confirm the key: key(DT) and tables(DT) should show the intended columns.
2. Compare correctness: compute the same result with base merge() or a dplyr pipeline, sort both consistently, and compare with all.equal().
3. Check side effects: data.table::address(DT) before and after setkey shows the same object; repeat after copy() to see the difference.
4. Measure, do not assume: use system.time() or bench::mark() on identical repeated lookups with and without the key.

Actionable closing: if a profile shows the same columns being filtered, joined, or grouped over and over, key those columns once, verify the key and the results, and measure the change. If the table is small or the operation runs once, skip the key and keep the original row order.