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

CREATE INDEX command in Amazon QLDB

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

In Amazon QLDB, use the CREATE INDEX command to create an index for a document field on a table.

To learn how to control access to run this PartiQL command on specific tables, see Getting started with the standard permissions mode in Amazon QLDB.

Important

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.

Note the following constraints when creating indexes:

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

  • You can create an index on any 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.

  • Query performance is improved only when you use an equality predicate; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789).

    QLDB doesn't 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.

Syntax

CREATE INDEX ON table_name (field)

Parameters

table_name

The name of 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 unique ID of the table that you created the index on.

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: