Amazon DynamoDB provides fast access to items in a table by specifying primary key values. However, to allow efficient access to data with attributes other than the primary key, you can use a secondary index, which is a data structure that contains a subset of attributes from a table and an alternate key. The index alternate key is made from a partition key and a sort key.

Every secondary index is associated with exactly one table from which it obtains its data. This is called the base table for the index.

The benefit of querying an index instead of the main table (if the index key has been specified in the criteria), is skipping a full scan of the main table. This makes the operation much faster.

Autodetection Algorithm

The auto-detection algorithm takes the following cases into consideration. If you have specified:

• an index key attribute in the criteria, the index is automatically detected, and it retrieves the items from the secondary index.
• more than one index key attribute in the criteria, an index is detected only if these attributes are part of the same index (i.e., are the partition key and the sort key of the index). Otherwise the data is selected from the main table.
• more than one key index attribute in the criteria, and if these key attributes are not keys of the same secondary index, the items are retrieved from the main table.
• the SecondaryIndexName pseudo column in the criteria, the specified value has priority and is the detected index.

Example

Here is an example of how secondary indexes are built and used:

Using Global Secondary Indexes in DynamoDB - Amazon DynamoDB

In the above example, the table GameScores has a secondary index, GameTitleIndex, which has as its partition key the GameTitle attribute. If you run this query:

SELECT * FROM GameScores WHERE GameTitle='Alient Adventure'

for AutoDetectIndex=true, it detects that the GameTitle is the partition key of the GameTitleIndex secondary index, and the following PartiQL (DynamoDB querying language) is executed, making the query faster by removing the need to scan the whole GameScores table:

SELECT * FROM "GameScores"."GameTitleIndex" WHERE GameTitle='Alient Adventure'

Turning off the AutoDetectIndex connection property executes:

SELECT * FROM "GameScores" WHERE GameTitle='Alient Adventure'

, which requires the full scanning of the data. You can use a the SecondaryIndexName pseudo column to specifically query a secondary index. This transforms this query:

SELECT * FROM Table WHERE SecondaryIndexName='index1'

to PartiQL:

SELECT * FROM "Table"."index1"