This feature (Lookup Tables) will only be available to CommCare users with a Standard Plan or higher. For more details, see the CommCare Software Plan page.
Indexes are designed to solve a specific problem: references to a large lookup table are slowing down your application. For example, suppose you are using a lookup table to Setup a Question with Filtered Choices. Your lookup table, health_centers_by_region, has the following fields:
and you have a hidden value that contains the calculation:
If this table is large enough that this filter is slowing down performance in your form, you may be able to alleviate this by adding an index to district_id and moving the indexed field to the front of your filter, so that:
An index is a data structure that helps make certain XPath expressions that reference a lookup table more efficient. When you index a field in a lookup table, you can improve the speed of expressions on that particular field. The following scenarios highlight the difference between querying a field that is indexed, compared to querying an unindexed field:
Most lookup tables will not need to be indexed. If you think you might need to index one or more fields of a lookup table, ask yourself:
If you answered "yes" to all of the above questions, it might be worth adding an index for one or more fields in your lookup table. If you're not sure whether a particular query is slowing down your app, try the following exercise:
If you are populating a multiple choice or checkbox question from a lookup table, and are still experiencing performance issues after indexing any appropriate columns from the filter, try adding indexes for the Value Field and the Display Text Field.
Suppose you have a lookup table health_centers_by_region with the following fields (this is the same table from the Overview above):
As part of your workflow, a mobile worker often needs to select a health center in a certain region that has MNCH services available. There are thousands of health centers spread over hundreds of districts, and you've noticed that a query that appears throughout the app is taking a long time:
Since district_id has hundreds of values represented, it has a high enough cardinality to warrant an index. mnch_services_available will only ever be set to "yes" or "no." Since the cardinality for this field is low, the benefits of indexing the field would be minimal compared to the extra space it would take up on the phone, so it should not be indexed.
Before adding an index to the lookup table, please refamiliarize yourself with the process of Creating and Updating Lookup Tables, specifically Downloading or Uploading Tables for Editing.
When you're ready to add an index, follow these steps:
Open the file and navigate to the types tab. It should look something like this:
|Delete(Y/N)||table_id||is_global?||field 1||field 2||field 3|
Add a column with header "field 1 : is_indexed?" and mark the column as "yes" for the field and table that you want to index:
|Delete(Y/N)||table_id||is_global?||field 1||field 1 : is_indexed?||field 2||field 3|
Once the index has been added, you will need to rewrite your query so that the indexed field or fields appear at the beginning of the string of filters. Since region_id is indexed and mnch_services_available is not, our example becomes:
See the next section for details.
When writing XPath queries on an indexed lookup table (or on any indexed fixture, such as casedb), it is important to write your query such that all filters on indexed properties are evaluated BEFORE other filters. For example, say you have a lookup table health_centers_by_region with the following fields:
If we want a query to return all health centers in region 123 where MNCH services are available, we would write:
With this query, the XPath query evaluator will first use an efficient key lookup to filter on the indexed field (region_id), before performing single lookups on each of the results to check whether mnch_services_available = "yes" for each one.
The following documents go into much further technical detail on XPath evaluation and optimization:
Currently, only the most basic XPath queries will leverage lookup table indexing. For example: value = 1 will use the indexing, but value < 2 will not. In addition, composite queries using and or or will not leverage the indexes.
When indexing a lookup table with multiple languages, you can only index fields that do not have translations. Indexing columns with attributes is unsupported.