function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
SaraagSaraag 

Indexed field on Case for filtering out closed cases

Hi All,

 

I'm struggling with a very large SOQL implementation. Among other things, I have to search through 3+ million cases, most of which are closed and are not relevant to me.I'm trying to identify the best field to use to filter out these closed cases? Currently, out of the 3+ million we have around 70,000 open cases.

 

Salesforce only mentions that some of the standard fields are indexed, but doesn't exactly specify which fields.

 

In terms of query optimization, is it better to use  isClosed=true or Status='Closed' or may be even both with an AND?

 

Saraag

Best Answer chosen by Admin (Salesforce Developers) 
GlynAGlynA

Saraag,

 

A warning about using 'isClosed':  'isClosed' becomes true the first time a Case is closed, and will not become false again, even if the Case is re-opened.  For this reason, I prefer to use "Status='Closed'".  If you use both, the AND will be redundant and lower your performance.  The same can be said for OR.  So use only one of the fields.  I don't know if either field is indexed.

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

All Answers

GlynAGlynA

Saraag,

 

A warning about using 'isClosed':  'isClosed' becomes true the first time a Case is closed, and will not become false again, even if the Case is re-opened.  For this reason, I prefer to use "Status='Closed'".  If you use both, the AND will be redundant and lower your performance.  The same can be said for OR.  So use only one of the fields.  I don't know if either field is indexed.

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

This was selected as the best answer
SaraagSaraag

Didn't know that, thank you. I hope status field is indexed, I'm going to keep this topic open incase someone  knows the answer.

 

Thanks for taking time out to help.

SaraagSaraag

I created a case with support. They said isclosed=false would be my best bet to filter out closed cases.

GlynAGlynA

"isClosed=false" has the advantage that if someone were to add a new Status picklist value that also closes the Case (e.g. "Cancelled"), the "isClosed=false" will exclude Cases with "Status='Cancelled'".  However, it still has the problem that if Cases are reopened (Status changed back to something other than 'Closed'), "isClosed" will remain true.  Just be aware of this.

 

-Glyn