Query performance: indexes and selectivity

Every chapter so far has been about getting the right rows back. This one is about getting them back fast — and, on big objects, getting them back at all. The good news: SOQL performance comes down to one idea, selectivity, plus a free tool that tells you whether your query has it. No DBA experience required.

Why a correct query can still be slow

Salesforce is a multi-tenant platform: your org shares database infrastructure with thousands of others, and the query optimizer's first job is protecting that shared hardware. You can't open a database console and add indexes yourself — you work with the indexes Salesforce provides, and you write filters those indexes can actually use.

When a WHERE clause can't use an index, Salesforce has no choice but to scan through the object's rows one by one. This query is perfectly correct:

SELECT Id, Name, AnnualRevenue
FROM Account
WHERE Industry = 'Technology'

On an org with ten thousand accounts, it's instant. On an org with five million, Industry isn't indexed, so the platform scans the whole object — and the same query crawls or gets rejected outright. Nothing about the syntax changed; the data underneath did. That's the core lesson of this chapter: performance lives in the WHERE clause, and it depends on your data, not just your query.

What's indexed out of the box

Salesforce maintains indexes on a predictable set of fields for every object:

For everything else — custom picklists, custom text and number fields, status fields — you have two options: mark the field unique or external ID in its definition (which creates an index as a side effect), or open a case with Salesforce Support and request a custom index on the field. Support grants these routinely when you can show a real query that needs one.

Selectivity in plain English

An index only helps if the filter using it narrows the results to a small fraction of the object. Filter five million cases down to two hundred and the index earns its keep; filter them down to three million and the index is pointless — the optimizer falls back to a scan even though the field is indexed. That's what selective means: the filter does real narrowing, relative to the size of the object.

On large objects — think hundreds of thousands of rows and up — this stops being a speed question and becomes a correctness question. An unselective query running in a trigger context can fail with the "Non-selective query against large object type" error, taking the whole transaction down with it. The exact cutoffs are the optimizer's business and shift with object size; the working rule is simple: on a big object, at least one indexed filter must cut the result set down to a small slice.

Filters that defeat indexes

Some filter shapes prevent the optimizer from using an index even when the field has one. The usual suspects:

• Leading-wildcard LIKE. LIKE '%consulting' can't walk an index, because an index is sorted from the start of the value. An anchored prefix can (see the LIKE chapter).
• Negative filters. != and NOT IN describe what you don't want, which is almost never a small slice of the object.
• Comparisons to null. Filters like Field = null generally can't use a standard index.
• OR across different fields. Each branch must be independently selective, which rarely holds in practice.
• Formula fields over unindexed fields. Filtering on a formula usually forces evaluation per row instead of an index lookup.

Index-defeating — the wildcard leads:

SELECT Id, Name FROM Account WHERE Name LIKE '%Consulting'

Index-friendly — anchored prefix on an indexed field:

SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'

Negative filters often have a positive rewrite. If you know the values you want, say so — enumerating them turns an open-ended exclusion into a closed list the optimizer can reason about:

Hard to optimize — everything except one value:

SELECT Id, CaseNumber FROM Case WHERE Status != 'Closed'

Easier — name the values you actually want:

SELECT Id, CaseNumber FROM Case
WHERE Status IN ('New', 'Working', 'Escalated')

Measure, don't guess: the Query Plan tool

You don't have to theorize about whether a query is selective — Salesforce will tell you. In the Developer Console, open Help → Preferences and tick Enable Query Plan. Now paste your query into the Query Editor and click the Query Plan button instead of executing it.

The plan lists each strategy the optimizer considered — an index it could use, or a full table scan — with a relative cost for each. Lower is better, and a plan whose cost comes in under 1 is considered selective. If the cheapest available plan is a table scan, you've learned something important before the query ever hit production: no filter in your WHERE clause is doing index-worthy work, and it's time to reshape it.

Practical fixes

When the plan says scan, work through these in order — the first two fix most cases:

• Add a date bound. CreatedDate and SystemModstamp are indexed on every object. If the business question is really about recent records, say so in the query.
• Lead with an indexed field. Combine your unselective filter with one on OwnerId, a lookup field, or an external ID. The indexed filter narrows the haystack; the rest of the clause refines it.
• Request a custom index. If you genuinely need to filter a big object by a custom status or category field, a Support case is the durable fix.
• Put a LIMIT on exploratory queries. While you're poking at data, cap the cost — and page through large result sets properly, as covered in ORDER BY, LIMIT, and OFFSET.
• Use the Bulk API for extracts. "Give me every row" isn't a query problem, it's an export problem, and the Bulk API is built for it.

Here, Status is unselective on its own, but an indexed owner filter and an indexed date bound make the query selective:

SELECT Id, CaseNumber, Subject
FROM Case
WHERE OwnerId = '005gK000004XyZAQA0'
  AND Status = 'New'
  AND CreatedDate = LAST_N_DAYS:90

Exploring? Cap it until you know the shape of the data:

SELECT Id, Name, Email
FROM Contact
WHERE MailingCountry = 'Germany'
LIMIT 200

With selectivity in your toolkit, one chapter remains: the error messages every SOQL writer eventually meets, and how to read them — on to common SOQL errors.