Example Code: Manual Index Creation: /Documentation

Example Code: Manual Index Creation

There are built-in indices on all data structures in LabKey Server which improve the performance of queries and other operations.

In some cases, manually adding an additional index can be useful to further improve performance. This topic describes how to add an index for a provisioned table like a dataset or list. You can import the table with an index specified in the metadata, or use the API. Note that you can only create additional indices. Update is not supported, and these manually defined indices are not included in folder or study export.

Considerations
Create a Dataset Index via Metadata
Use the Domain Creation APIs for Datasets and Lists

R (Rlabkey)
JavaScript
Python

Considerations

An administrator should balance the benefits of a manual index against the potential costs. A few considerations to keep in mind:

Use caution not to edit or disable any predefined indices. The system relies on these and may not work properly if they are removed.
Once an index is in place you may experience problems modifying the column in the designer or APIs. If you needed to rename a column, for example, you would first need to remove the index and could add it again after making the table change.
Indices will slow down operations like insert/update/delete on the table itself. Only add a minimal number of indices where these tradeoffs will improve overall performance. Indices can also make deadlocks more frequent if the tables are being accessed by multiple requests in parallel.
When adding unique indices, or other database constraints, be aware that the system is not expecting these constraints to be present. Any errors that occur may not be reported in a user friendly manner.
Further, subject to the limits of the underlying database, you cannot define a unique index on a 'large' column. If you need a unique index on a string column and see an error related to this limit, see if you can reduce the max field length in the designer and retry.

Create a Dataset Index via Metadata

To add a custom index to a dataset, you can include it in the "datasets_metadata.xml" file. When that file is read during import or study reload, the index will be created.

For example, the following will add two indices: a non unique one on the column "PrimaryLanguage" and a unique one on the column "BadgeNum".

<indices>
  <index type="non-unique">
    <column>PrimaryLanguage</column>
  </index>
  <index type="unique">
    <column>BadgeNum</column>
  </index>
</indices>

To add an index on a dataset, you can make use of the study reload process as follows:

In your study, confirm the dataset you want to index has the columns you need of the correct types.
From the Manage tab, click Export Study.
Under Export to: select Pipeline root export directory, as individual files.
Click Export.
After the export, open the nodes export > study > datasets to find the datasets_metadata.xml file.
Edit this file to add the index in the section for the desired dataset, immediately following the </columns> section.
Back in your server file content module, select the file "study.xml" in the study folder.
Click Import Data, select Reload Study, and click Import.
The reload process will add the index to your dataset.
You can confirm the presence and properties of all indices on the dataset in the schema browser.

Select the schema/dataset, then click View Raw Table Metadata.
Scroll down to Other Index Metadata and you will see your indices included.

Use the Domain Creation APIs for Datasets or Lists

Both Dataset and List creation support the inclusion of manual indices. You can use:

R (Rlabkey)
JavaScript
Python

R (Rlabkey)

Support in the R API was added with Rlabkey version 2.4.0. Use the helper function createIndices, following this format:

labkey.domain.createIndices(colNames, asUnique, existingIndices = NULL)

Arguments to createIndices:

colNames: A list of string column names for the index
asUnique: A logical TRUE or FALSE value for if a UNIQUE index should be used
existingIndices: A list of previously created indices definitions to append to

Then, use these indices in the createDesign API following this pattern:

labkey.domain.createDesign(name, description = NULL, fields, indices = NULL)

Arguments to createDesign:

name: A string specifying the name of the domain
description (optional): A string specifying domain description
fields: A list containing the fields of the domain design, this should be in the same

format as returned by labkey.inferFields.

indices (optional): A list of indices definitions to be used for this domain design on creation

JavaScript

Use LABKEY.Domain.create and include the indices in the domain template. Learn more about usage, parameters, etc. in the documentation:

Python API

Use create.domain() and include a list_domain_definition that specifies your indices. For example, to create a unique index on a column named 'field_index' in a list named 'Blood Types', you might use the following:

list_domain_definition = {
    'kind': 'IntList', # or 'VarList'
    'domainDesign': {
        'name': 'BloodTypes',
        'description': 'All known human blood types',
        'fields': [{
            'name': 'rowId',
            'rangeURI': 'int'
        },{
            'name': 'type',
            'rangeURI': 'string'
        },{
            'name': 'field_index',
            'rangeURI': 'string'
        }],
        'indices' : [{
                'columnNames' : ['field_index'],
                'unique' : True
            }
        ]
    },
    # 'options' allows for passing properties specific to a domain type (e.g. list, dataset, etc)
    'options': {
        'keyName': 'rowId',
        'keyType': 'AutoIncrementInteger'
    }
}

created_list_domain = domain.create(server_context, list_domain_definition)

Learn more in the Python API documentation on GitHub.

LabKey Support

LabKey Support