The Problem of Non-Selective Queries

A SOQL query that seems fine in a dev org can time out in a production org with 10 million records. The difference is selectivity — the percentage of records the query’s WHERE clause filters down to.

Salesforce’s query optimizer refuses to use an index if a query is non-selective, even when the index technically exists on the filtered field. The reason: scanning the table is faster than using a poorly-selective index. So the query full-scans, and in large orgs, it times out or hits the 50,000-row query limit.

Understanding selectivity is how you write SOQL that scales.

Selectivity Thresholds

Salesforce publishes specific selectivity rules, summarized here:

  • Standard indexed fields (Id, Name, RecordTypeId, and a handful of others): selective if the filter matches ≤ 30% of records, capped at 1 million records total.
  • Custom indexed fields (including external IDs and indexed lookups): selective if ≤ 10% of records match, capped at 333,000 records.

Above these thresholds, the optimizer falls back to a full scan.

What “Indexed” Means in Salesforce

Every object has implicit indexes on these fields: Id, Name, OwnerId, CreatedDate, LastModifiedDate, SystemModstamp, RecordTypeId, master-detail and lookup foreign keys, and fields marked as External ID, Unique, or explicitly indexed via support.

You cannot create arbitrary indexes via the UI. For non-standard indexed fields, open a Salesforce support case requesting a custom index. They evaluate the request and — if the field qualifies (high cardinality, right data type) — apply the index.

Checking Query Performance

Use the Query Plan tool in the Developer Console:

  1. Developer Console → Query Editor.
  2. Enable Query Plan (toggle at top-right).
  3. Run your SOQL.
  4. Review the output — each candidate plan has a cost and an explanation of why the optimizer chose (or skipped) each path.

Look for plans that say Index with a low cost. A cost above ~1 means the optimizer fell back to a table scan or sharing enforcement.

Sharing Rules and Selectivity

Even with an indexed, selective filter, queries can slow down when the user has complex sharing (many groups, role hierarchy, manual shares). Salesforce applies sharing enforcement via the sharing tables, which themselves must be selective.

Orgs with millions of sharing rows and deep role hierarchies feel this most. Mitigations:

  • Run queries as an integration user with View All or Modify All permissions (skips sharing enforcement).
  • Use WITH SYSTEM_MODE in Apex where appropriate.
  • Flatten shares via ownership changes if sharing is genuinely not needed.

Common Fixes for Slow Queries

1. Add Selective Filters

A query with WHERE Status = 'Open' is non-selective if 80% of records are Open. Add more filters:

SELECT Id FROM Case 
WHERE Status = 'Open' 
  AND OwnerId = :userId 
  AND CreatedDate = LAST_N_DAYS:90

Now the query combines three filters that together are selective.

2. Use Indexed Fields in WHERE

If a field isn’t indexed, filtering on it is a full scan regardless of selectivity.

Common mistake: filtering on a custom picklist with no index when a related indexed field would do the job. Refactor queries to use indexed fields.

3. LIMIT Aggressively

LIMIT 10 on a full scan is still a full scan — the scan happens before the limit applies, in general. But for ORDER BY on an indexed field with LIMIT, the optimizer can use the index for sort + cutoff.

SELECT Id FROM Account 
ORDER BY CreatedDate DESC 
LIMIT 100

This is a fast pattern because CreatedDate is indexed and the ordering terminates at 100 rows.

4. Use Skinny Tables for Reporting

Salesforce can create skinny tables for high-volume reporting queries. A skinny table is a denormalized projection of selected fields. Queries restricted to skinny-table fields skip joins and run faster.

Skinny tables are created by Salesforce support, not self-service. They’re appropriate for orgs with 2M+ records on an object and clear reporting patterns.

5. Avoid Negative Filters

Filters like !=, NOT IN, and EXCLUDES never use indexes. Rewrite when possible:

-- Non-indexable
WHERE Status != 'Closed'

-- Indexable (enumerate positive options)
WHERE Status IN ('Open', 'Pending', 'Waiting on Customer')

6. Don’t Use LIKE With Leading Wildcards

WHERE Name LIKE '%foo%' cannot use an index. WHERE Name LIKE 'foo%' can.

For substring search at scale, use SOSL (full-text search) instead of SOQL LIKE.

Large Data Volume (LDV) Tactics

For objects with millions of records:

  • PK chunking (via REST or Bulk API) lets you paginate by Id ranges efficiently.
  • Skinny tables for reports and exports.
  • Divisions as a last resort to partition data by business unit.
  • Archiving — move old records to a separate object or off-platform storage.

At this scale, query design is inseparable from data architecture.

The SELECT * Antipattern

SOQL doesn’t support SELECT *, which is a blessing. But the equivalent bad habit — selecting every field explicitly — has the same effect. Large field lists increase heap, serialization, and bandwidth.

Select only the fields you need. Audit queries in hot paths and prune unused fields.

Queries Inside Triggers

A slow query in a trigger affects every DML on that object. Even a 200-millisecond query on a hot object can degrade user-facing performance.

Budget trigger queries carefully:

  • Index every field you filter on.
  • Limit to what’s needed.
  • Consider async paths for queries that aren’t needed in the transactional path.

Profiling at Scale

For orgs with persistent performance issues:

  • Use Event Monitoring to see slow queries.
  • Use Developer Console debug logs to profile at the individual transaction level.
  • Escalate persistent issues to Salesforce support — they have internal tools (like the Query Plan Analyzer) that can suggest custom indexes.

Frequently Asked Questions

Can I force the optimizer to use an index?

No. Salesforce’s query optimizer is cost-based and not manually overridable.

How do I know if a field is indexed?

Standard indexed fields are documented. For custom indexes, check with your admin or support. External ID, Unique, and indexed-on-request fields are indexed.

Does adding more WHERE clauses always help?

Only if the added filters are themselves selective and indexable. Adding a non-selective filter can actually slow the query.

Should I use @ReadOnly for complex reports?

@ReadOnly increases query row limits (from 50K to 1M) for reporting scenarios. Use it for batch reads when you don’t need to write back.

Share