Creating an index

Compare the CREATE INDEX statement in SQL with the UpdateTable operation in Amazon DynamoDB.

SQL

In a relational database, an index is a data structure that lets you perform fast queries on different columns in a table. You can use the CREATE INDEX SQL statement to add an index to an existing table, specifying the columns to be indexed. After the index has been created, you can query the data in the table as usual, but now the database can use the index to quickly find the specified rows in the table instead of scanning the entire table.

After you create an index, the database maintains it for you. Whenever you modify data in the table, the index is automatically modified to reflect changes in the table.

In MySQL, you can create an index like the following.

CREATE INDEX GenreAndPriceIndex
ON Music (genre, price);

DynamoDB

In DynamoDB, you can create and use a secondary index for similar purposes.

Indexes in DynamoDB are different from their relational counterparts. When you create a secondary index, you must specify its key attributes—a partition key and a sort key. After you create the secondary index, you can Query it or Scan it just as you would with a table. DynamoDB does not have a query optimizer, so a secondary index is only used when you Query it or Scan it.

DynamoDB supports two different kinds of indexes:

• Global secondary indexes – The primary key of the index can be any two attributes from its table.

• Local secondary indexes – The partition key of the index must be the same as the partition key of its table. However, the sort key can be any other attribute.

DynamoDB ensures that the data in a secondary index is eventually consistent with its table. You can request strongly consistent Query or Scan actions on a table or a local secondary index. However, global secondary indexes support only eventual consistency.

You can add a global secondary index to an existing table, using the UpdateTable action and specifying GlobalSecondaryIndexUpdates.

{
    TableName: "Music",
    AttributeDefinitions:[
        {AttributeName: "Genre", AttributeType: "S"},
        {AttributeName: "Price", AttributeType: "N"}
    ],
    GlobalSecondaryIndexUpdates: [
        {
            Create: {
                IndexName: "GenreAndPriceIndex",
                KeySchema: [
                    {AttributeName: "Genre", KeyType: "HASH"}, //Partition key
                    {AttributeName: "Price", KeyType: "RANGE"}, //Sort key
                ],
                Projection: {
                    "ProjectionType": "ALL"
                },
                ProvisionedThroughput: {                                // Only specified if using provisioned mode
                    "ReadCapacityUnits": 1,"WriteCapacityUnits": 1
                }
            }
        }
    ]
}

You must provide the following parameters to UpdateTable:

• TableName – The table that the index will be associated with.

• AttributeDefinitions – The data types for the key schema attributes of the index.

• GlobalSecondaryIndexUpdates – Details about the index you want to create:

  • IndexName – A name for the index.

  • KeySchema – The attributes that are used for the index's primary key.

  • Projection – Attributes from the table that are copied to the index. In this case, ALL means that all of the attributes are copied.

  • ProvisionedThroughput (for provisioned tables) – The number of reads and writes per second that you need for this index. (This is separate from the provisioned throughput settings of the table.)

Part of this operation involves backfilling data from the table into the new index. During backfilling, the table remains available. However, the index is not ready until its Backfilling attribute changes from true to false. You can use the DescribeTable action to view this attribute.

Note

For code examples that use UpdateTable, see Getting started with DynamoDB and the AWS SDKs.