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.
For custom-indexed fields, the limit is 10% of the first million records + 5% of the remaining records, but no more than 333333 records.
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.
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.