Add or delete indices

ca_db_indices(x, tbl, idx_fld_add = NULL, idx_fld_del = NULL, quiet = FALSE)



Either a remote tibble or a SQLite database file name


The name of a table in the SQLite database


Fields in tbl to create an index for


Fields in tbl that have indices you'd like to delete


Suppress messages




Database indices improve performance when you filter or sort rows based on field (column), and/or join tables based on a common field. By default, indices are not created when you download Cal-Adapt data into a SQLite database with ca_getvals_db (because they increase the size of the SQLite file). You can tell ca_getvals_db to create indices with the indices argument, or use ca_db_indices to create indices after data are downloaded.

x can be either a remote tibble returned by ca_getvals_db, or a SQLite database file name. tbl should be the name of a table in the database (i.e., the db_tbl argument you passed to ca_getvals_db. If you're not sure what the table names are in a database, run ca_db_info. Normally you would only add indices to a table that contains values from Cal-Adapt (there is no need to add indices to lookup tables). You can only add indices for one table at a time (but idx_fld_add can contain multiple field names).

Note that ca_db_indices can only create indices on a single field. To create composite indices you can run SQL expressions with the DBI package. Indices added by ca_db_indices will be named automatically.

For more details, see the vignette on querying large volumes of data: vignette("large-queries", package = "caladaptr")