I am starting a new series on Salesforce things you should know. Not sure how long I can do this but surely try to keep up. To start with lets jump into one of the salesforce’s most overlooked topics when it comes to Queries – Salesforce Selective Queries and Indexes.

What “selective” really means in Selective Queries and Indexes

A selective query is one whose filter uses an indexed field and returns few enough rows that the optimizer chooses that index instead of scanning the whole table.

Salesforce automatically indexes:

1. Primary keys: Id
2. Foreign keys: Lookup and Master-Detail fields (AccountId, OwnerId, etc.)
3. Audit fields: CreatedDate, SystemModstamp, LastModifiedDate
4. Custom fields marked External Id or Unique

Other fields can get a custom index through Salesforce Support, but only if the filter is deterministic and useful.

The selectivity thresholds

Salesforce uses internal thresholds to decide if an index is worth using. Think of it as “how many rows can your filter touch and still be considered selective.”

Standard index threshold

• 30% of the first 1,000,000 rows, plus
• 15% of all rows after 1,000,000,
• Capped at 1,000,000 rows total.

If your filter returns no more than that many rows, a standard index can drive the plan. If it returns more, Salesforce usually skips the index and scans instead.

Examples:

• 50,000 rows total → allowed = 30% of 50,000 = 15,000
• 500,000 rows → allowed = 30% of 500,000 = 150,000
• 1,200,000 rows → 30% of 1,000,000 = 300,000, plus 15% of 200,000 = 30,000 → total 330,000
• 2,500,000 rows → 300,000 + 15% of 1,500,000 = 225,000 → total 525,000
• 10,000,000 rows → math says 300,000 + 1,350,000 = 1,650,000, but hard cap applies → 1,000,000 max

In one line: 30% of the first million + 15% of the rest, capped at one million.

Custom index threshold

• 10% of the first 1,000,000 rows, plus
• 5% of all rows after 1,000,000,
• Capped at 333,333 rows total.

Quick feel:

• 1,200,000 rows → 10% of 1,000,000 = 100,000, plus 5% of 200,000 = 10,000 → total 110,000
• 10,000,000 rows → 100,000 + 5% of 9,000,000 = 450,000, but cap → 333,333 max

Custom indexes are stricter. That’s why you often need tighter filters when you rely on them.

How the optimizer decides

• If your WHERE clause includes a filter on an indexed field and the estimated matched rows are under the threshold, the index is a win and gets used.
• If the estimate exceeds the threshold, the optimizer tends to ignore the index and perform a full or large scan.
• When you have multiple filters, the optimizer evaluates combinations that reduce the row count the most. A single very selective predicate can carry the plan.

Read Also: Agentforce is not enough for Salesforce Developer

Writing selective filters that win

Use these every day:

1. Equality on indexed fields

SELECT Id FROM Case WHERE AccountId = :acctId

SELECT Id FROM Case WHERE AccountId = :acctId

Equality on Id, lookups, External Ids, and Unique fields is gold.

2. Range on audit dates

WHERE CreatedDate >= :start AND CreatedDate < :end

WHERE CreatedDate >= :start AND CreatedDate < :end

Date ranges on indexed audit fields are often selective if the window is small enough.

3. LIKE with a leading anchor

WHERE Name LIKE 'Acme%'

WHERE Name LIKE 'Acme%'

Acme% can use an index. %Acme% cannot.

4. Avoid negative filters for selectivity

-- Bad for selectivity
WHERE Status != 'Closed'

-- Bad for selectivity
WHERE Status != 'Closed'

Not-equals, NOT IN, and OR across wide sets are often non-selective.

5. Beware of formulas

• Formula fields are not indexed. Filtering on them is usually non-selective.
• Move the logic into a real field you can index, or filter on the underlying indexed columns.

6. Avoid NULL traps

• Selectivity with = NULL or != NULL is tricky and often not selective. Prefer explicit states you can index.

7. Reduce the working set early

• Add record type, status, owner, or date window filters first so the optimizer sees a small candidate set

Quick cheat sheet for Selective Queries and Indexes

• Standard index: 30% of first 1M + 15% of the rest, cap 1,000,000.
• Custom index: 10% of first 1M + 5% of the rest, cap 333,333.
• Equality and anchored LIKE on indexed fields are your friends.
• Formulas are not indexed. External Id and Unique custom fields are indexed.
• Negative filters and wide ORs kill selectivity.
• Date ranges on audit fields are a reliable way to stay selective.

How to verify your plan

• Use Query Plan in Developer Console or Workbench to see:
  • Which index is considered
  • Estimated rows (Cardinality)
  • Cost and whether the plan is marked selective
• If the cardinality is above the threshold, refactor your filter or add a better index.

In part 2 of Salesforce things you should know, we will go through the Query Plan tool in Developer Console. Also, If you remember one thing from this post, make it this: Selective Queries and Indexes decide performance. Keep your filters on indexed fields and under the thresholds, and your queries will scale.