Salesforce SOQL/SOSL Selectivity & Query Plan — Standard, Custom & Compound Indexes

If you want blazing-fast SOQL, your filters have to be selective. In practice, that means your WHERE clause narrows things enough for Salesforce to rely on an index. Use the Query Plan tool to see what the optimizer intends to do and shape your filters to hit standard, custom, or compound indexes.

Selectivity & the Query Plan (how Salesforce decides)

Core idea

A query is “selective” when its leading filters match a small slice of the table. When the optimizer estimates a low cost, it’ll choose an indexed plan. Open Query Plan and look at:

• Leading Operation Type (e.g., Index vs. TableScan)

• Cardinality (estimated rows the leading filter will match)

• sObject Cardinality (total rows in the object)

• Cost (lower is better)

Real-world example

A slow Case list view gets snappy when you swap a wide text search for equality on an indexed field plus a date window.

// Before (non-selective: broad LIKE + OR)
List<Case> bad = [
  SELECT Id, Subject
  FROM Case
  WHERE Subject LIKE '%renewal%' OR Status != 'Closed'
  ORDER BY CreatedDate DESC LIMIT 200
];

// After (selective: indexed equality + range)
Id recType = Schema.SObjectType.Case.getRecordTypeInfosByName().get('Support').getRecordTypeId();
Date since = Date.today().addDays(-30);
List<Case> good = [
  SELECT Id, Subject, Status
  FROM Case
  WHERE RecordTypeId = :recType            // indexed equality
    AND CreatedDate >= :since              // indexed datetime
    AND IsClosed = false
  ORDER BY CreatedDate DESC
  LIMIT 200
];

Tip: In Developer Console → Query Plan, confirm Leading Operation Type = Index and the Cost is low.

Standard indexes (what you already have)

Core idea

Salesforce ships with indexes on a bunch of fields:

• Id, OwnerId, CreatedDate, SystemModstamp, RecordTypeId, Division

• Lookup/Master-Detail FKs (e.g., AccountId on Contact)

• External Id / Unique fields (when you configure them)

Real-world example

“Recent opps by account” gets fast with two indexed equalities.

public with sharing class OppRepo {
  public static List<Opportunity> byAccountSince(Id accountId, Date since) {
    return [
      SELECT Id, Name, Amount, StageName, CloseDate
      FROM Opportunity
      WHERE AccountId = :accountId          // indexed FK
        AND LastModifiedDate >= :since      // indexed datetime
      ORDER BY LastModifiedDate DESC
      LIMIT 200
    ];
  }
}

Tips: Prefer = and IN on indexed fields. Don’t wrap fields in functions in WHERE (e.g., avoid CALENDAR_YEAR(CreatedDate)).

Custom indexes (when standard isn’t enough)

Core idea

If your frequent filters aren’t indexed, add External Id/Unique on a custom field (those are auto-indexed) or request a custom index from Salesforce Support. The field must be deterministic (not a formula depending on non-indexed fields) and used with selective operators.

Real-world example

You search Orders by CustomerRef__c (string) and Region__c. Make CustomerRef__c an External Id; request an index on Region__c if volume is high.

// Assume CustomerRef__c is External Id (indexed)
public with sharing class OrderRepo {
  public static List<Order__c> findByCustomerRef(String ref, String region) {
    return [
      SELECT Id, Name, Status__c, Region__c
      FROM Order__c
      WHERE CustomerRef__c = :ref           // indexed equality
        AND Region__c = :region             // (custom index requested if high volume)
      LIMIT 100
    ];
  }
}

Tips:

• Avoid leading wildcards ('%term') and negative operators (NOT, !=)—they’re usually non-selective.

• If you truly need text search, stick to prefix LIKE ('term%') or use SOSL.

Compound (composite) indexes (optimize common filter combos)

Core idea

When you always filter with the same multi-field pattern, ask for a compound index. Put equality/IN filters on the left-most fields and keep any range comparison at the end. That lets the optimizer prune aggressively.

Real-world example

You often fetch Contacts by AccountId + IsActive__c and a recent LastModifiedDate. A compound index on (AccountId, IsActive__c, LastModifiedDate) is a great fit.

public with sharing class ContactRepo {
  public static List<Contact> activeForAccount(Id accountId, Date since) {
    return [
      SELECT Id, Name, Email, LastModifiedDate
      FROM Contact
      WHERE AccountId = :accountId          // left-most equality
        AND IsActive__c = true              // next equality
        AND LastModifiedDate >= :since      // trailing range
      ORDER BY LastModifiedDate DESC
      LIMIT 200
    ];
  }
}

Design tips: Keep left-most fields highly selective, and make sure your queries almost always include them.

Write queries that stay selective (common pitfalls & fixes)

Core idea

Certain patterns force table scans even if you have indexes. Rewrite them to keep the query index-friendly.

Real-world examples & fixes

// 5a) Leading wildcard kills index
// Bad:
[SELECT Id FROM Account WHERE Name LIKE '%tech%' LIMIT 50];
// Good (prefix LIKE is indexable in many cases, or use SOSL):
[SELECT Id, Name FROM Account WHERE Name LIKE :('tech%') LIMIT 50];

// 5b) Function on field prevents index
// Bad:
[SELECT Id FROM Opportunity WHERE CALENDAR_YEAR(CloseDate) = 2025];
// Good:
Date startY = Date.newInstance(2025,1,1), endY = Date.newInstance(2025,12,31);
[SELECT Id FROM Opportunity WHERE CloseDate >= :startY AND CloseDate <= :endY];

// 5c) OR across unindexed fields
// Bad:
[SELECT Id FROM Case WHERE Status = 'New' OR Subject LIKE :('Renew%')];
// Good: split or ensure both sides are selective/indexed; or use IN on a single field
[SELECT Id FROM Case WHERE Status IN ('New','Open')];

// 5d) Negative filters
// Bad:
[SELECT Id FROM Contact WHERE Email != null AND DoNotCall = false];
// Good: positive checks with indexed fields
[SELECT Id FROM Contact WHERE DoNotCall = false AND Email != null];

Operational tips:

• Check Query Plan after changes or data growth.

• Add skinny tables only after tuning (they don’t replace indexes).

• Re-review selectivity after data skew (e.g., one Owner with millions of records).

Selectivity: the golden rule for efficient queries

Selectivity means shaping your WHERE clause to narrow the search as early as possible, ideally using indexed fields with sargable predicates. When you do, the optimizer can jump straight to matches instead of scanning the whole object.

Quick cheat sheet for selective WHERE clauses:

* Null checks can be selective if the field is indexed, but often aren’t when it’s not.

SOQL vs. SOSL: pick the right tool

Both are query languages, but they solve different problems.

• SOQL is for targeted retrieval when you know the object(s). It’s precise and great for structured results, single objects, and parent/child relationships.

Real-world example: making SOQL selective

You need high-priority contacts for a given account.

❌ Non-selective (slow on big data sets): uses a non-indexed text field with a wildcard.

// AVOID: This query is not selective and will perform poorly with many records.
List<Contact> nonSelectiveContacts = [
    SELECT Id, Name, Email
    FROM Contact
    WHERE Description LIKE '%High Priority%' // Non-indexed field with a wildcard
];

// GOOD: This query is highly selective.
Id targetAccountId = '001Dn00000B4eXzIAJ';
List<Contact> selectiveContacts = [
    SELECT Id, Name, Email
    FROM Contact
    WHERE AccountId = :targetAccountId // Uses the standard index on the lookup field
    AND Level__c = 'Primary' // Use a picklist or indexed text field
];

• SOSL shines for keyword searches across objects. It’s built for text: Salesforce tokenizes and indexes text behind the scenes for speed.

Real-world example: global text search

// Use SOSL to perform a text search across multiple objects.
String searchTerm = 'Acme*'; // Use wildcard for partial matches

List<List<SObject>> searchResults = [
    FIND :searchTerm IN NAME FIELDS
    RETURNING
        Account(Id, Name, Industry),
        Contact(Id, Name, Email WHERE Account.Name != null),
        Opportunity(Id, Name, StageName)
];

// SOSL returns a list of lists, one for each object.
List<Account> foundAccounts = (List<Account>)searchResults[0];
List<Contact> foundContacts = (List<Contact>)searchResults[1];
List<Opportunity> foundOpps = (List<Opportunity>)searchResults[2];

System.debug('Found ' + foundAccounts.size() + ' matching accounts.');

// 1) SOSL for keyword discovery
List<List<SObject>> r = [FIND 'reset MFA' IN ALL FIELDS
  RETURNING Case(Id WHERE Status != 'Closed' LIMIT 200)];

// 2) SOQL to fetch details of candidates
Set<Id> ids = new Map<Id, Case>((List<Case>) r[0]).keySet();
List<Case> details = [
  SELECT Id, Subject, Priority FROM Case WHERE Id IN :ids
];