---
title: "Why your query got slow at two million rows"
url: "https://trailassembly.com/why-your-query-got-slow/"
topic: "Architecture"
published: "2026-09-13T07:27:44+00:00"
updated: "2026-10-06T13:54:15+00:00"
---

# Why your query got slow at two million rows

**In one sentence:** your query did not get slow, your data got big. A filter that matched 40,000 rows out of 300,000 was selective enough to use an index. The same filter matching 800,000 rows out of two million is not, so the query optimiser stopped using the index and started scanning the table. Nothing in your code changed. The row count crossed a threshold, and the platform silently switched strategy.

## The thresholds, which are the whole story

Salesforce decides whether a filter is selective by counting how many rows it would match, then comparing that count against a threshold that depends on the index type.

|  | Standard index | Custom index |
| --- | --- | --- |
| First million rows | 30% | 10% |
| Rows beyond the first million | 15% | 5% |
| Absolute cap | 1,000,000 rows | 333,333 rows |

Standard indexes cover Id, Name, OwnerId, CreatedDate, SystemModstamp, RecordTypeId, master-detail and lookup fields, and fields marked unique or as external IDs. Custom indexes are the ones Salesforce Support adds on request, and they are held to a much tighter standard.

Work an example on a custom object with two million rows and a [custom index](https://trailassembly.com/selective-queries-at-scale/) on `Status__c`. The threshold is 10% of the first million (100,000) plus 5% of the second million (50,000), so 150,000 rows. A status value on 120,000 records uses the index. The same value on 160,000 records does not, and the query goes to a full scan.

## Why it feels like a cliff

Because it is one. There is no gradual degradation. The filter is selective or it is not, and on the wrong side of the line the database reads every row in the table. On a two million row object that is the difference between 200 milliseconds and a query timeout.

The cap makes this worse at the top end. Beyond roughly 8.6 million rows a standard index can never be used by a filter matching more than a million records, regardless of percentage. Your 12% filter is fine at four million rows and useless at fifteen million.

## Confirming it rather than guessing

Run the query through the [Query Plan](https://trailassembly.com/salesforce-query-plan-tool/) tool in Developer Console. Turn it on under Preferences, then run the SOQL in the Query Editor and click Query Plan.

You get a row per available plan with a cost figure. Below 1.0 means the optimiser expects to use that plan. A leading plan of type `TableScan` with a cost above 1.0 tells you exactly what has happened.

```apex
SELECT Id, Name FROM Invoice__c
WHERE Status__c = 'Open'
AND CreatedDate = LAST_N_DAYS:30
```

Two filters, and the optimiser picks the more selective one to drive the query. Adding the date filter often rescues a query whose status filter has gone non-selective, because CreatedDate has a standard index and thirty days of data is a small slice of five years.

## The things that quietly destroy selectivity

- **Negative operators.** `!=`, `NOT IN`, `NOT LIKE` and `EXCLUDES` cannot use an index at all. A filter of `Status__c != 'Closed'` is a table scan no matter how few closed records exist.

- **Leading wildcards.** `LIKE '%acme'` is unindexable. `LIKE 'acme%'` is fine.

- **Nulls.** Standard indexes do not include null rows, so `WHERE Field__c = null` forces a scan unless Support has built a null-aware custom index for you, which they can.

- **OR across different fields.** Every branch of the OR has to be selective, or the whole thing scans.

- **Formula fields** that reference other objects, or anything non-deterministic. They cannot be indexed.

## What to actually do

First, make the filter narrower rather than making the index better. A date bound is the cheapest win available, because CreatedDate and SystemModstamp are already indexed and almost every query has a defensible time window.

Second, ask Support for a custom index on the field, and be honest with yourself about whether the field will stay selective. An index on a two-value picklist buys you nothing once both values are common.

What does not help: adding `LIMIT`. The optimiser decides the plan before the limit applies, so a scan is still a scan.

## Frequently asked

**Does the recycle bin count towards the row count?** Yes. Soft-deleted records sit in the table until they age out, and they count towards selectivity calculations. Emptying the bin on a large purge is a real optimisation.

**Can I create a custom index myself?** Not directly. Marking a field as [External ID](https://trailassembly.com/upsert-with-external-ids/) or Unique creates one as a side effect, which is the usual workaround. Anything else goes through a Support case.

**Why does the same query run fine in the sandbox?** Because a [Partial Copy](https://trailassembly.com/salesforce-sandbox-strategy/) sandbox has a fraction of the rows, so every filter is selective. Sandbox performance testing on a scaled-down dataset tells you nothing about this class of problem.

---

Source: https://trailassembly.com/why-your-query-got-slow/
Trail Assembly — independent Salesforce write-ups.