Search
Twitter
« Salesforce Summer '11 Training | Main | Dreamforce for Developers »
Friday
Jun102011

Standard Indexed Fields

I was recently doing some consulting for a company that works with Salesforce managed packages.  They’d come across a problem that had turned into one heckuva knot they were having trouble unraveling.  Turns out the issue had to do with some obscure details about Salesforce indexing of fields on its standard objects.  These details were only discovered via many emails and conversations with Salesforce support.

When Salesforce developers and admins write queries that are so broad that they bring back “too much” data, they often get an error message like this:

The following exception was encountered:  System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

The workaround is to try and create tighter queries by using a standard Salesforce field that has been indexed.  But even if a field is indexed, a filter might still not be selective when the filter value includes null (for instance binding with a list that contains null), or data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times).

If at all possible you want to construct your queries using the standard Salesforce indexed fields or work with Salesforce to index a column that you are going to use frequently in your lookups.

The standard Salesforce indexed fields are:

  •  Lead: Company, Email, Lead Owner, Name.
  • Contact: Account Name, Contact Owner, Email, Name, Reports To.
  •  Account: Account Name, Account Owner, Account Record Type, Parent Account,
  •  On top of these, system time fields (last modified, created date etc) and any lookup fields are also indexed.

 

What happens if I need more indexed fields in Salesforce?

By default your primary keys, forgeign keys, custom fields marked as External Ids/Unique along with the system audit dates (like last modified) are indexed. If you have a specific field that you need indexed, you can reach out to Salesforce support. Keep in mind though that there is a limit to how many you can add and they are going to weigh the pros and cons with you to see if this is going to help your situation.

Certain fields cannot be used as indexes. Examples include: multi-option picklists, currency fields if the org has multi-currency enabled, certain formula fields, binary fields (like a blob or file). In general any complex field type is not a candidate.

 

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (5)

Can you tell me, where did you find information about these specific indexed fields, because I tried to find this in the cheat sheet: http://s3.amazonaws.com/dfc-wiki/en/images/0/0e/Db-query-search-optimization-cheat-sheet.pdf and in the salesforce_apex_language_reference.pdf and I wasn't able to find further information about the specific indexed fields.
Thx in advance.

April 19, 2013 | Unregistered CommenterAElek

Aelek, thanks for the feedback. I'm sorry to let you know that I didn't pull this info from any one reference, but from years of working with Salesforce. Good luck!

May 1, 2013 | Registered Commentersfgeneral

Rather strangely Lookup fields do not appear to be indexed as standard, do you know if it is possible to have a customer index on them

September 19, 2013 | Unregistered Commenterv100

John, I'm not positive if a user can index on look-up fields, but I'll be looking into it!
Thanks for the feedback!

September 27, 2013 | Registered Commentersfgeneral
AElek, thanks for posting the link to the "Database Query & Search Optimization Fields Cheat Sheet."
FYI, that link is still functional, but there's also a new "help.salesforce.com" link here: http://help.salesforce.com/help/pdfs/en/salesforce_query_search_optimization_developer_cheatsheet.pdf
October 31, 2013 | Unregistered CommenterReader

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Hyperlinks will be created for URLs automatically.