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.
Salesforce has a great resource on their engineering site related to indexed fields. I highly recommend it: