Creating Key Indexes

This topic explains how to create a key index for queries in VMware Tanzu GemFire.

Creating a key index is a good way to improve query performance when data is partitioned using a key or a field value. You can create key indexes by using the createKeyIndex method of the QueryService or by defining the index in cache.xml. Creating a key index makes the query service aware of the relationship between the values in the region and the keys in the region.

The FROM clause for a primary key index must be just a region path. The indexed expression is an expression that, when applied to an entry value, produces the key. For example, if a region has Portfolios as the values and the keys are the id field of the Portfolios region, the indexed expression is id.

You can then use the FunctionService (using the partitioned key as a filter passed to the function and as part of the query equality condition) to execute the query against the indexed data. See Optimizing Queries on Data Partitioned by a Key or Field Value for more details.

There are two issues to note with key indexes:

• The key index is not sorted. Without sorting, you can only do equality tests. Other comparisons are not possible. To obtain a sorted index on your primary keys, create a functional index on the attribute used as the primary key.
• The query service is not automatically aware of the relationship between the region values and keys. For this, you must create the key index.

Note: Using a key-index with an explicit type="range" in the cache.xml will result in an exception. Key indexes cannot be used in range queries

Examples of Creating a Key Index

Using Java API:

QueryService qs = cache.getQueryService();
qs.createKeyIndex("myKeyIndex", "id", "/exampleRegion");

Using gfsh:

gfsh> create index --name=myKeyIndex --expression=region1.Id --region=region1 --type=key

Using cache.xml:

<region name=exampleRegion>
  <region-attributes ...>
  </region-attributes>
  <index name="myKeyIndex" from-clause="/exampleRegion" expression="id" key-index="true" type="key"/>
  ...
</region>

Note: If you do not specify the type of index, the type defaults to range.