0

I was reading SFDC documentation and they state that using != or Contains will almost run the query in table scan.

With that said, we have 5 Million case records in production. When i check the query plan for below SOQL, it shows rows with table scan & index scan but as per the docs, the lowest cardinality cost is used for the query being executed. This means that the index scan was used.

Can someone confirm if the above is true ?. Please see the query plan result and the SOQL below :

Select RecordType.DeveloperName, CaseNumber, ContactId, RecordType.Name, Contact.Name,  SystemModStamp 
from Case 
where AccountId ='XXXXXXXXXXXXX' 
    and id != 'XXXXXXXXXXXXX' 
    and (RecordType.DeveloperName = 'XXXXXX' OR RecordType.DeveloperName = 'XXXXXX') 
order by SystemModStamp Desc limit 5

enter image description here

0

Yes, the query will use the most efficient query by Cost. Your query will not run a TableScan. Take a look at this Knowledge Article for better understanding of how the Query Plan tool works:

Developer Console Query Plan Tool FAQ

...

What does it all mean?

The Query Plan Tool will show a list of available plans that our Query Optimizer can use for the query provided and will be arranged by cost ascending.

Each Plan will contain information on Cardinality, Operation Type, Cost, sObject Type, and more. Each plan has a 'Leading Operation Type', for example, Field Index or Full Table Scan. The plan with the lowest cost is the plan that is used for driving the query execution.

Again, please note:

The plan with the lowest cost is the plan that is used for driving the query execution.

  • Thank you for the reply. So why the documentation states that != will not make the query selective ? Also. Why it shows that filter Id != is not optimizable and the owner is not optimizable ? Please explain – Darkdog Jun 7 '19 at 15:40
  • If it is your only filter, the query will certainly not be selective in your case. – Adrian Larson Jun 7 '19 at 15:41
  • I am sorry i didn't get you what you are saying. Can you explain a little more in detail ?. As per the query plan, the query should be using index but why is it showing that Id != is not optimizable. Does that mean that the query is going to use table scan ? – Darkdog Jun 7 '19 at 15:50
  • yep i read that article. all i want to know is that why it was showing that Id != is not used for optimizable. I guess just like isDeleted, that message is also irrrelevant. Thank you – Darkdog Jun 7 '19 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.