Adding index on field - Answers - Salesforce Trailblazer Community
Ask Search:
Jerun JoseJerun Jose 

Adding index on field

I need to know how to add an index on a field.
The indexing is required as there are multiple fields that are used in the 'where clause' of queries in multiple classes.

I do not wish to make these fields required and also I do not use them as an external ID. There are more than 10 fields in an object where I need to add an index so that the processing does not get slow.

Thanks and regards,

Jerun Jose
Best Answer chosen by Moderator (salesforce.com) 
Jerun JoseJerun Jose
Salesforce.com support team gave me an answer for this. And I quote

Salesforce has the ability to place a custom database index on most standard and custom fields. Keep in mind that a custom index may not always improve query times for an organization. In some cases, it could even make the query slower. 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.

NOTE: Your requests will be reviewed. We have to ensure that the index will be useful.


So that means that yeah, custom indexing is possible, but it will be setup through the SFDC support team.

All Answers

Matt BrownMatt Brown
If this is a developer question you will need to post this on the Developer Discussion Boards. http://boards.developerforce.com/sforce?category.id=developers
Oriana LiskerOriana Lisker
Hi Jerun - did you figure out how to do this?  I'm interested in doing the same.
Jerun JoseJerun Jose
Salesforce.com support team gave me an answer for this. And I quote

Salesforce has the ability to place a custom database index on most standard and custom fields. Keep in mind that a custom index may not always improve query times for an organization. In some cases, it could even make the query slower. 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.

NOTE: Your requests will be reviewed. We have to ensure that the index will be useful.


So that means that yeah, custom indexing is possible, but it will be setup through the SFDC support team.
This was selected as the best answer
Ria DavidsonRia Davidson
Is there a way to remove indexing?
Kashyap KalakbandiKashyap Kalakbandi
@Ria Davidson 

Contact Salesforce support for un-indexing the fields.