Why secondary indexes enable flexible queries in DynamoDB

Introduction

Secondary indexes let you search your data in new ways without changing your main table. They make your queries more flexible and faster.

You want to find items by a different attribute than the main key.

You need to sort or filter data using another column.

You want to run queries without scanning the whole table.

You want to improve performance for specific search patterns.

You want to keep your main table structure simple but still support many queries.

Syntax

DynamoDB

CREATE GLOBAL SECONDARY INDEX index_name ON table_name (partition_key, sort_key);

-- Or when creating a table:
CREATE TABLE table_name (
  primary_key_attribute TYPE,
  other_attribute TYPE,
  ...
) WITH GLOBAL SECONDARY INDEXES (
  index_name (partition_key_attribute, sort_key_attribute)
);

Secondary indexes can have their own partition and sort keys.

Global Secondary Indexes (GSI) allow querying by different keys than the main table.

Examples

This creates a table where the main key is Artist and SongTitle, but you can also query by AlbumTitle and Year using the AlbumIndex.

DynamoDB

CREATE TABLE Music (
  Artist STRING,
  SongTitle STRING,
  AlbumTitle STRING,
  Year INT,
  PRIMARY KEY (Artist, SongTitle)
) WITH GLOBAL SECONDARY INDEXES (
  AlbumIndex (AlbumTitle, Year)
);

This query uses the secondary index to find all songs in the album 'Greatest Hits' quickly.

DynamoDB

SELECT * FROM Music.AlbumIndex WHERE AlbumTitle = 'Greatest Hits';

Sample Program

This example creates a Books table with ISBN as the main key. It adds a secondary index on Author and Title so you can find all books by a specific author quickly.

DynamoDB

CREATE TABLE Books (
  ISBN STRING,
  Title STRING,
  Author STRING,
  Genre STRING,
  PRIMARY KEY (ISBN)
) WITH GLOBAL SECONDARY INDEXES (
  AuthorIndex (Author, Title)
);

-- Insert sample data
INSERT INTO Books VALUES ('123', 'Learn SQL', 'Alice', 'Education');
INSERT INTO Books VALUES ('456', 'Cooking 101', 'Bob', 'Cooking');
INSERT INTO Books VALUES ('789', 'Advanced SQL', 'Alice', 'Education');

-- Query by Author using secondary index
SELECT * FROM Books.AuthorIndex WHERE Author = 'Alice';

Important Notes

Secondary indexes increase storage and write costs because data is duplicated.

They help avoid slow full table scans by enabling targeted queries.

Remember to choose index keys based on your query needs.

Summary

Secondary indexes let you query data in new ways without changing the main table.

They improve query speed and flexibility by using different keys.

Use them to support multiple search patterns efficiently.