Understanding how data extensions are indexed is crucial for optimizing performance and query efficiency. Proper indexing enhances query performance, speeds up data retrieval, and ensures efficient data management.
What Is an Index?
An index is used to designate specific columns (fields) in a table as valuable for data evaluation. Indexes cannot be directly applied by users of the Marketing Cloud. An index is always applied to fields designated as a Primary Key.
Self-Indexing
Most procedures using Data Extensions are capable of automatically self-indexing. For example:
- Sends: When you send an email, the system automatically indexes the relevant fields.
- Filters: Filters applied to Data Extensions also trigger self-indexing. Creating a filtered DE forces the source DE to self-index.
- AMPScript: Functions like
LookupRows
orLookup
implicitly create indexes; the email must be sent though.
Primary Key and Indexing
A Primary Key is a field that uniquely identifies each record in a Data Extension. By default, the Subscriber Key is set as the Primary Key. Any field designated as a Primary Key is automatically indexed. You can manually set a different field as the Primary Key during Data Extension creation.
Adding New Fields and Indexing
When adding new fields to a Data Extension, consider the following:
- Batching: Add up to 4 new fields at a time to a new Data Extension.
- Existing Data Extensions: If adding more than 4 fields to an existing Data Extension, do so in batches of 4.