1

When we use AND condition in our SOQL query and lets assume both the fields are indexed and both are standard index.

eg) Select id from child__c where RecordType.Name = 'a' and parent__c ='awx9979880dcWSD'

  • In this the first filter(record_type) will always return more than 20% of the object's records
  • Second filter will return less than 20% in fact less than 5%

I have two links where the explanation for this scenario contradicts

  1. Link 1 : Search for "For AND, the query optimizer uses the indexes unless one of them returns more than 20% of the object’s records or 666,666 total records."

So even if any one of the index filters returns more than 20% query optimizer wont use the index

  1. Link 2 :Go to the bottom of the page Query 3.

"SELECT Id FROM Account WHERE Name != '' AND CustomField__c = 'ValueA'"

Here we have to see if any filter, when considered individually, is selective. As we saw in the previous example, the first filter isn't selective. So let's focus on the second one. If the count of records returned by SELECT COUNT() FROM Account WHERE CustomField__c = 'ValueA' is lower than the selectivity threshold, and CustomField__c is indexed, the query is selective.

Please let me know where is my understanding going wrong. Thanks in advance.

1 Answer 1

1

In the first link, the part you quoted was talking only about custom indexes. Standard indexes use the rules in the preceding paragraph:

Standard Indexed Fields

Used if the filter matches less than 30% of the first million records and less than 15% of additional records, up to a maximum of one million records.

As such, there is no contradiction.

Just to be clear, as long as a field meets the index's criteria, that index will be used, even if other ones are not.

To clarify further, let's change your criteria so that you're using custom indexes:

Select id from child__c where IndexField1__c = 'a' and ParentFormulaId__c ='awx9979880dcWSD'

Now that we have two custom indexes in use (assume the formula has a custom index), as long as either index has less than 20% of the total record volume, that index will be used to determine selectivity.

However, your original query would instead abide by the rules above; as long as Parent__c is used in less than 30% of the first million records and 15% of additional records, up to 1,000,000 total, the index will be used and considered for selectivity calculation.

3
  • We have found that some well defined and selective queries have very poor performance for large data sets due to something called a "prequery" performed by Salesforce against a related table (only used for ID matching in the query itself). The prequery could take over 120 seconds, thus causing a timeout. This resulted in the need to introduce skinny tables (by Salesforce) in the affected orgs. It's worth being aware of, even when the query plan doesn't draw attention to it.
    – Phil W
    Feb 21, 2022 at 8:55
  • Thank you for your reply. For the above example which you stated for custom index, Assuming total records is 1,000,000 , IndexField1__c is returning 500,000(50%) and ParentFormulaId__c is returning 100,000(10%). According to this statement "For AND, the query optimizer uses the indexes unless one of them returns more than 20% of the object’s records or 666,666 total records." SF wont use index because IndexField1__c is returning more than 20%. But according to you it will use the Index. Please correct me if i am wrong.
    – R Ramki
    Feb 21, 2022 at 10:15
  • @RRamki No, in your example, ParentFormulaField__c index would be used, but Index1__c would not. Each index is considered individually if it will be used or not.
    – sfdcfox
    Feb 21, 2022 at 14:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .