2

I've read the docs for query selectivity, but I am still a little unsure as to how it will act for a single query using more than 1 filter in a series of AND statements.

From my analysis with the Query Planner I can see that 2 of the filters are very selective and are indexed, but another requires a full table scan.

enter image description here

Select Id
From MyObject__c
Where Very Selective Filter For Indexed Field
    And Very Selective Filter For Indexed Field
    And Non-Selective Filter For Non-Indexed Field

What I'm unsure about is whether the query as a whole is considered selective. My assumption about the way that it would work is that it would use the indexes to narrow down the records, and would then do a scan of that much smaller sub-set of records for the Non-Selective, Non-Indexed field.

However, from the cheatsheet:

AND
Filter targets less than:
- Twice the index selectivity thresholds for each filter.
- The index selectivity thresholds for the intersection of the fields.

So, if the Non-Selective filter on it's own amounts to more than twice the index selectivity threshold for that SObject.

Just to confirm, does that mean that the query as a whole is unselective and therefore that indexes won't be used?

Thanks

  • If you are getting cost > 1 then the query is considered as non-selective even index are used. If you post the screenshot I would be able to explain. – Ashwani Dec 29 '15 at 12:44
  • @Ashwani - Thanks. I have added the query analysis. – Joe Dec 29 '15 at 12:56
3

As per the given screen shot, Your query is selective. According to Salesforce docs

If a SOQL query contains at least 1 selective filter, the query is said to be selective. If the SOQL query doesn't contain a selective filter, the query is said to be un-selective and will require a full table scan.

You have two Selective Filters 'RecordTypeId' and 'Status__c'. Query Plan will use field 'RecordTypeID' for its plan.

In notes Indexes are not considered for field which have Lead Operation Type 'Other' and 'TableScan'.

If you use field only which have Lead Operation Type 'Other' and 'TableScan' it will become non-selective because they exceed the threshold value and query planner has no option. Adding one selective filter will make the query selective. However, Salesforce has internal process for optimizing queries so sometimes it may show time-out error.

  • Thanks @Ashwani. How do you think the statement from the docs re AND statements that I posted above affects my query. The third filter is more than twice the index selectivity threshold. Will this not make my query non-selective? This is on the cheat-sheet at: s3.amazonaws.com/dfc-wiki/en/images/0/0e/… – Joe Dec 29 '15 at 13:52
  • @Joe Query Optimisation will use least cost as filter in db so, query becomes selective. If it has filter query and cost more than 1 then it would be considered as non selective. Did you try running that query? Also its cardinality is 4 which is far from 1M. – Ashwani Dec 29 '15 at 14:59

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.