top of page
Blog
  • Writer's pictureOleg Minko

Indexing in Salesforce: how to make your Salesforce faster


Does your Salesforce work not as fast as you expected? Do you suspect there might be some problem with Salesforce?


If you work with a large amount of data, the process might be slow because it is hard for the system to deal with each piece of data quickly and efficiently. Such problems might appear while working with reports and list views. The performance of reports and list views depends on Salesforce SOQL queries speed execution. The time processing of SOQL queries is a significant part of working with Salesforce. To reach a higher speed of your Salesforce, we recommend you structure the database with the help of Salesforce indexing. Such an upgrade of the databases simplifies the navigation and ensures your satisfaction with using Salesforce.


How does indexing in Salesforce work?


If you want to work with the Salesforce database more efficiently, you use selective queries, because non-selective queries might have long execution times on non-indexed fields. You can use selective queries In reports and list views by applying filters, in SOQL queries - using WHERE clause with indexed fields. That's what the selective query might look like:


SELECT Id, Name, Some_Custom_Field__c FROM Account WHERE Name = 'Some name' OR RecordTypeId = '0125a000000000RAAY'

The point is that when you use the names of indexed fields in the WHERE part of the query, then the indexes will be applied. But if you add any NOT indexed field in the query, indexing won't be applied, and the search won't make any sense in terms of its optimization.

An additional table is created for an indexed field in the database of Salesforce. The table contains:

  • a copy of the data from the indexed field

  • information about its type

  • a link to the corresponding row in the original table

Such an approach aims to order the data in the table, which allows users to apply more efficient search algorithms instead of looking over all the records in a row.


Salesforce indexes limitations


Index search doesn't always work. It has some limitations, and also the search might be pretty time-intensive because the system needs to find a record in the index table and select it from the original table. It is worth using index searches only when less than 30% of the rows from the requested table match the request. If more than 30% of rows match - the indexing search doesn't make sense.

For standard fields that are indexed initially, indexes are used for selections that contain up to 30% of the first million records, + 15% of the remaining records, but no more than one million.


Force Query Optimizer - Custom Index | Sparkybit Blog

For custom-indexed fields, the limit is 10% of the first million records + 5% of the remaining records, but no more than 333333 records.


Force Query Optimizer - Standard Index | Sparkybit Blog

If these conditions are met, indexes can be effectively and successfully used in database query optimization.


The SOQL query with the WHERE condition should be formed correctly. The query won't work in the following cases:

  • when using negative operators (!=, NOT LIKE, EXCLUDES);

  • when there is a comparison of text fields with the operators >, <, >=, <=;

  • when using the "%" symbol at the beginning of the string (for example, field__c LIKE "%string").

Indexing fields in Salesforce


First of all, there are standard fields in Salesforce that are indexed by default:

  • RecordTypeId

  • Division

  • CreatedDate

  • Systemmodstamp (LastModifiedDate)

  • Name

  • Email (for contacts and leads)

  • Foreign key relationships (lookups and master-detail)

  • Salesforce record ID, which is the primary key for each object

  • External Ids

These fields have separate tables in the Salesforce database, which helps speed up database queries.


And of course, in Salesforce, users can create custom fields and index them, but this option has some limitations. The following type of fields can NOT be indexed:

  • Multi-select picklists

  • Text areas (long)

  • Text areas (rich)

  • Encrypted text fields

  • Non-deterministic formula fields

Conclusion


Index search helps users take more benefits from using Salesforce due to speeding up query processing. When a field in a database is indexed, its values are saved in a more coherent data structure. Users can work with fields indexed by default and also create custom-indexed fields.


Oleg Minko, CTO at Sparkybit
Alexey Nayda, CEO at Sparkybit


Ready to challenge us?

Be welcome to reach out and share your ideas and requests. We are here to help you with all the Salesforce-related challenges.



Comments


Commenting has been turned off.
bottom of page