CREATE INDEX - Amazon Quantum Ledger Database (Amazon QLDB)
SyntaxParametersReturn valueExamplesRunning programmatically using the driver

CREATE INDEX

Use the CREATE INDEX statement to create an index for a document field on a table in your Amazon QLDB ledger.

Indexes are optional in QLDB, but we strongly recommend using them to improve query performance for seek operations. Note the following constraints when creating indexes:

  • Indexes can only be created on a single top-level field. Composite, nested, unique, and function-based indexes are currently not supported.

  • Query performance is improved only when you use an equality predicate; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789). Be sure to enclose the list of values in parentheses when you use the IN operator. Without the parentheses, the statement invokes a table scan rather than an indexed lookup.

    QLDB doesn't currently honor inequalities in query predicates. As a result, range filtered scans are not implemented.

  • Names of indexed fields are case sensitive and can have a maximum of 128 characters.

  • Index creation in QLDB is asynchronous. The amount of time it takes to finish building an index on a non-empty table varies depending on the table size. For more information, see Managing indexes.

Warning

QLDB requires an index to efficiently look up a document. Without an index, QLDB needs to do a full table scan when reading documents. This can cause performance problems on large tables, including concurrency conflicts and transaction timeouts.

To avoid table scans, you must run statements with a WHERE predicate clause using an equality operator (= or IN) on an indexed field or a document ID. For more information, see Optimizing query performance in the Working with data and history topic.

Syntax

CREATE INDEX ON table (field)

Parameters

table

The table where you want to create the index. The table must already exist.

The table name is case sensitive.

field

The document field name for which to create the index. The field must be a top-level attribute.

Names of indexed fields are case sensitive and can have a maximum of 128 characters.

You can create an index on any Amazon Ion data types, including list and struct. However, you can only do the indexed lookup by equality of the whole Ion value regardless of the Ion type. For example, when using a list type as an index, you can't do an indexed lookup by one item inside the list.

Return value

tableId – The ID of the table on which you created the index.

Examples

CREATE INDEX ON VehicleRegistration (LicensePlateNumber)
CREATE INDEX ON Vehicle (VIN)

Running programmatically using the driver

To learn how to programmatically run this statement using the QLDB driver, see the following tutorials in Getting started with the driver: