Add or delete indices

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

Arguments

x

Either a remote tibble or a SQLite database file name

tbl

The name of a table in the SQLite database

idx_fld_add

Fields in tbl to create an index for

idx_fld_del

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

quiet

Suppress messages

Value

x

Details

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")