You are here:
Optimize Queries with Secondary Indexes
With a secondary index, created on a Data Lake Object (DLO), Data 360 quickly finds and returns data based on defined secondary field values. A secondary index improves performance and reduces credit usage.
Data 360 automatically indexes DLOs. The index of the Profile and Other categories is the primary key, while Event Time Field is the index of the Engagement category. These indexes are the primary key field. This indexing method optimizes data storage and retrieval based on the values in these fields. You can use a secondary index to filter and join data by using fields other than the primary key field, which improves query performance.
A secondary index, in Data 360, is a batch data transform on the provided DLO. This batch data transform adds the specified field as the new secondary index.
Use Cases and Best Practices
Consider this guidance when you create a secondary index.
- A secondary index can improve query speed for large DLOs that you frequently filter on a non-primary field.
- Secondary Indexes work best for queries that filter on fields with large numbers of distinct values. Queries on these fields should return nearly unique objects. For example, customer-id, phone number, email, or “Global ID” fields.
- Use a secondary index to quickly search a range of data in a large DLO. For example you can use a secondary index to search for customers in a defined zip code range.
- You can create multiple secondary indexes on one DLO. This ensures that queries against the DLO can use the most efficient index.
- Consider packaging your secondary index configurations in Standard Data Kits for easy sharing and consistent configuration.
Secondary Indexes and SQL
- Data Model Object (DMO) queries that filter on a field mapped to a DLO secondary index field use the secondary index to improve query performance.
- In
WHEREclauses (filters), secondary indexes only work with a direct match on the indexed field. The indexed field can’t be based on calculations, functions, or fuzzy patterns. - To join two large objects on a non-primary field, secondary indexes benefit from one of the objects having a small number of rows.
- Filters with a secondary index and another field perform best when you use the AND clause. The secondary index can reduce the set of values to query before querying the other field.
Limitations
Consider these limitations when you define your secondary indexes.
- Data 360 refreshes secondary indexes daily. Each time an object is updated, the secondary index becomes stale until the next refresh. Your query first scans the secondary index before scanning the new values in the base object.
- Secondary indexes work best on Engagement DLOs or DLOs that update at most every 24 hours.
- Secondary indexes don't support Zero-Copy Data 360 objects or unstructured DLOs.
- You can’t create a secondary index in a companion org. However, queries from a companion org to a DLO in the shared data space of the home org use the defined secondary index.
- Secondary indexes don’t support DevOps Data Kits or Managed Packages.
Credit Consumption
- To support the cost of index creation and daily index refresh, we recommend at least 10,000 queries a day on the target DLO.
- Queries that use a secondary index may consume more credits than the number of returned records. Data 360 queries groups of your DLO’s records, and each group must be processed fully even if it doesn’t match your query.
Next Steps
- Billing Considerations for Secondary Indexes
Use of secondary indexes consumes data services credits. Use of secondary indexes impacts the consumption of credits used for billing in these usage types. - Create a Secondary Index
Learn how to create secondary indexes in Data 360 to significantly improve query performance and reduce credit usage on Data Lake Objects (DLOs). Optimize data retrieval beyond primary key fields.
