Why a query that was fast at 100,000 rows is slow at 5 million

The short answer: your filter stopped using its index. The query optimiser only uses an index when the filter matches fewer rows than a fixed threshold, and that threshold is a share of the table. A filter that matched 5% of 100,000 rows still matches 5% at 5 million rows, but 5% of 5 million is 250,000 rows, and that is past the line. Nothing warns you. The query just starts scanning the table.

The thresholds are published, and they are lower than most people assume

Salesforce documents the exact numbers. An index gets used when the filter matches:

Both are documented on the indexes page of the large data volumes guide. Two things follow from them that change how you design.

First, a custom index is stricter than a standard one, not looser. People add a custom index expecting more headroom and get less. The custom index is there so you can index a field that has no index at all, not so you can push a wide filter further.

Second, the threshold is a moving target. Your query does not get slower because the table got bigger. It gets slower because your filter crossed a percentage boundary while you were not looking. Growth is the trigger, and growth is silent.

Read the query plan before you change anything

Guessing at selectivity wastes days. The Developer Console will tell you. Open Help | Preferences, set Enable Query Plan to true, then run your query in the Query Editor and press Query Plan.

You get a row per plan the optimiser considered, with two columns that matter:

• Leading Operation Type. Index means it used an index. TableScan means it read the whole table. Sharing means it drove the query from the sharing tables for the running user. Other is an internal optimisation.
• Cost. This is the query’s cost measured against the selectivity threshold. Under 1 is selective. Over 1 is not, and you are getting a scan.

That is the whole diagnostic. A cost of 0.8 is fine. A cost of 3 means the filter is matching roughly three times more rows than the threshold allows, and no amount of rewriting the SELECT clause will change it — you have to change what the WHERE clause matches.

One caveat worth knowing before you trust the number: the plan is calculated for the user running it. A system administrator with access to everything and a support agent with access to a slice of the data can get different plans for the same query. Test as the profile that actually runs the query in production.

Four filter shapes that lose the index quietly

These are the ones that catch teams out, in the order I see them.

A leading wildcard. WHERE Name LIKE '%acme%' cannot use an index, because an index is ordered from the start of the value. A trailing wildcard, LIKE 'acme%', can. If you need to match on the middle of a string, that is a search problem, not a query problem, and SOSL is the tool.

Negative operators. !=, NOT LIKE and NOT IN ask for everything except a small set, which is the opposite of selective. Turn the filter around and list what you do want. Status__c IN ('Open','Pending') is selective in an org where most records are closed; Status__c != 'Closed' is not, even though it returns the same rows.

OR across different fields. Every field in an OR needs its own index, and every branch has to be selective on its own. One unindexed branch drops the whole query to a scan. Two queries plus a merge in Apex is often faster than one clever query, which feels wrong and is still true.

Nulls. By default an index does not contain rows where the field is empty, so WHERE Custom_Field__c = null has nothing to look in. Support can index nulls for a single-column custom index if you ask. In a two-column index, nulls in the second column are already included.

Fix the filter before you ask for an index

The order matters, because the cheap fixes are also the fast ones.

1. Add a second, narrow condition. A date bound is the usual winner. CreatedDate = LAST_N_DAYS:90 on top of your existing filter often takes the row count under the threshold on its own, and it costs nothing but a code change.
2. Change the field you filter on. A picklist with three values spread evenly across 5 million rows can never be selective. A checkbox that is true for 2% of rows can. If a query has to be fast, design a field whose job is to be selective — a flag, set by automation, that marks the rows the query wants.
3. Ask Support for a custom index. Real, and free, and slower to arrive than a code change. Bring your query plan output to the case. It is the evidence that gets it approved quickly.
4. Ask about a skinny table. This is the heavier option: Salesforce builds a copy of your table containing only the fields you name, so the query stops joining across the standard and custom field tables. Up to 200 columns, only certain field types, and it leaves out soft-deleted rows, which is usually a bonus. The catch that bites: skinny tables are copied to Full sandboxes only, so your partial-copy sandbox will not reproduce production performance, and adding a field to the query needs another case to update the definition.
5. Move the data. Archiving old rows out of the object is the only fix that makes the percentages work in your favour again, because it shrinks the denominator.

What this means for design, not just for debugging

An architect can use the thresholds as a design rule rather than a repair kit.

If you know an object will hold millions of rows, decide during design which queries have to stay fast, then make sure each of those has a filter that will still match under 10% of the table in three years. That is a data model question (which field, what values, who sets it), and it is much cheaper to answer before the object exists.

The pattern I would push for: one indexed, deliberately skewed field per high-volume object, set by automation, whose only purpose is to make the important queries selective. A boolean Needs_Processing__c that is true for a few thousand rows out of ten million will outperform any amount of query tuning on a status picklist.

The alternative (and I have watched this happen more than once) is a team that spends a quarter tuning SOQL, gets a custom index on every field in the WHERE clause, and still has a slow page, because the filter was never going to be selective at that table size no matter which index it used.

Frequently asked

Does adding LIMIT make a query selective? No. The optimiser decides on the filter, not on how many rows you plan to keep. LIMIT 1 on a non-selective filter still scans.

Do reports and list views follow the same thresholds? They use the same optimiser and the same indexes, which is why a slow report and a slow SOQL query usually have one shared cause.

Is a formula field indexable? Only when it is deterministic — the value cannot depend on anything that changes outside the record’s own save, so no TODAY(), no cross-object references to fields that move independently. If you need to filter on it at volume, write the value to a real field instead.

Will a two-column index solve an OR? No. Two-column indexes help when both fields appear in an AND. An OR still needs each branch to be selective by itself.