Local Secondary Index (LSI) concept in DynamoDB

Introduction

A Local Secondary Index (LSI) helps you find data in a DynamoDB table using a different sort order without copying all the data again.

You want to search items by the same main key but sort them differently.

You need to quickly find recent orders for a customer sorted by date.

You want to filter data using another attribute but keep the main key the same.

You want to run queries with different sorting options without extra tables.

Syntax

DynamoDB

CREATE TABLE TableName (
  PartitionKeyName PartitionKeyType,
  SortKeyName SortKeyType,
  OtherAttributes ...
) WITH LOCAL SECONDARY INDEX (
  IndexName,
  PartitionKeyName,
  AlternateSortKeyName
);

The partition key must be the same as the main table's partition key.

The sort key in LSI is different from the main table's sort key.

Examples

This creates an LSI named OrderDateIndex to sort orders by OrderDate for each CustomerID.

DynamoDB

CREATE TABLE Orders (
  CustomerID STRING,
  OrderID STRING,
  OrderDate STRING,
  TotalAmount NUMBER
) WITH LOCAL SECONDARY INDEX (
  OrderDateIndex,
  CustomerID,
  OrderDate
);

This LSI lets you find songs by the same artist but sorted by album title.

DynamoDB

CREATE TABLE Music (
  Artist STRING,
  SongTitle STRING,
  AlbumTitle STRING
) WITH LOCAL SECONDARY INDEX (
  AlbumIndex,
  Artist,
  AlbumTitle
);

Sample Program

This table stores books by author and title. The LSI YearIndex lets you query books by the same author but sorted by the year they were published.

DynamoDB

CREATE TABLE Books (
  Author STRING,
  Title STRING,
  YearPublished NUMBER
) WITH LOCAL SECONDARY INDEX (
  YearIndex,
  Author,
  YearPublished
);

Important Notes

LSIs share the same partition key as the main table but use a different sort key.

You can only create LSIs when you create the table; you cannot add them later.

LSIs help you query data in multiple ways without duplicating data.

Summary

LSI lets you sort data differently using the same partition key.

It helps find data faster with alternate sort keys.

LSIs must be defined when creating the table.