MemberMay 31, 2017 at 9:06 am
The list below details some important criteria to see if your request is a good fit for a custom index.
1. Record count for the indexed object must be greater than 1,000 rows (greater than 10,000 rows is recommended to be of significant benefit).
2. Queries should take 5 seconds or more to process. Note that sometimes queries are slow the first run, then fast due to caching of the query results.
3. Query must contain a WHERE clause with a value that somewhat unique (i.e., the custom index should be able to filter out ~ 90% of the records). For example if the query is [SELECT AccountId FROM Contact WHERE LastName = 'Smith'], less than 10% of the Contact records can be 'Smith' for the custome index to be effective. For queries without WHERE clauses, custom indexes will not help performance.
4. Some commonly used fields, external id fields, and reference (lookup/master-detail) fields are already standard indexed. Formula fields and certain other fields cannot be custom indexed.
5. Customers can request that a particular field be indexed; however, they need to understand that it is not always beneficial to index all or some the field(s) they have requested.
6. For SOQL queries executed in Apex, it is common for developers to embed data binding variables to make the query dynamic; however, for analyzing the query for custom indexing, we need to know the typical values that will actually be used at runtime. For example, in the customer's Apex classes or triggers, they might have a query that looks like this:
Opportunity opp = [SELECT Name FROM Opportunity WHERE Amount > :minValue AND Amount < :maxValue];
Before anlyzing the query, we need to know what the typical values for :minValue and :maxValue will actually be. Please provide a few sample values and incidate if any of them will be null.