data.table Using keys and indices

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Introduction

The key and indices of a data.table allow certain computations to run faster, mostly related to joins and subsetting. The key describes the table's current sort order; while each index stores information about the order of the table with respect a sequence of columns. See the "Remarks" section below for links to the official vignettes on the topic.

Remarks

The official vignettes are the best introduction to this topic:

Keys vs indices

A data.table can be "keyed" by a sequence of columns, telling interested functions that the data is sorted by those columns. To get or set the key, use the functions documented at ?key.

Similarly, functions can take advantage of a data.table's "indices." Each index -- and a table can have more than one -- stores information about the order of the data with respect a sequence of columns. Like a key, an index can speed up certain tasks. To get or set indices, use the functions documented at ?indices.

Indices may also be set automatically (currently only for a single column at a time). See ?datatable.optimize for details on how this works and how to disable it if necessary.

Verification and updating

Missing values are allowed in a key column.

Keys and indices are stored as attributes and may, by accident, not correspond to the actual order of data in the table. Many functions check the validity of the key or index before using it, but it's worth keeping in mind.

Keys and indices are removed after updates where it's not obvious that sort order is preserved. For example, starting from DT = data.table(a=c(1,2,4), key="a"), if we update like DT[2, a := 3], the key is broken.



Got any data.table Question?