Aller au contenu
SteveTechArcSteveTechArc Courses

When Salesforce Uses an Index

Aussi en 19 langues

English · Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · Português (Portugal) · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Українська · Español (México) · Deutsch (Schweiz)

A baggage carousel carrying a line of black suitcases, with one bright yellow suitcase among them.
Image générée par IA

Module 3 of 5 in the chapter Query and Retrieval Architecture, chapter 8 of the Data Architect course. Each module stands on its own. ⟲ Précédent : Comment Salesforce trouve vos données · Suivant : Index, skinny tables et reconception

The question this module answers: When can Salesforce use an index? ⟲

In the last module we built the card catalog: what an index is, the cost of every copy on every save, and which fields Salesforce indexes for you, which ones you can ask for, and which ones can’t have an index at all. So now we have an index. Can it be used in all circumstances? No. Let me tell you a story first. ⟲

The Nitro Boost

Section intitulée « The Nitro Boost »

Picture a street race. Two cars side by side, engines roaring. Near the end, one driver presses a button and the car jumps forward and wins. That button is the nitro: a boost of extra fuel that gives the engine a burst of speed for a few seconds. ⟲

But the nitro doesn’t fire just because you press the button. A nitro system is usually built so it only fires when three things are true at once: ⟲

  1. The system is installed and switched on. No nitro in the car, no boost. ⟲
  2. The driver has the pedal all the way down. Press it halfway, and the system stays off. ⟲
  3. The engine is in the right speed range. Too slow or too fast, and the system won’t fire. ⟲

All three, and the car jumps ahead. Miss any one, and you’re just driving. ⟲

An index is your query’s nitro. And it works the same way: three things have to be true before Salesforce uses it. ⟲

The carYour query
The nitro is installed and switched onThere’s an index on the field you filter on
The pedal is all the way downYou ask in a selective way: equals, starts with
The engine is in the right rangeNot too many records match: you’re under the threshold

This module takes them one at a time. Let’s start with the first. ⟲

Is There an Index?

Section intitulée « Is There an Index? »

Let’s start with the question the catalog was built for. Walk up to the desk and ask for Plumbing Basics. The librarian goes to the drawer of cards by title, finds the card, reads the shelf (B4), walks over and takes the book down. One card, one book. In a library of 10 million books, that’s one card and one book instead of 10 million books. That’s what an index is for, and it’s what Salesforce does when you filter on an indexed field: find the entry, read which record it points to, go straight there. ⟲

Now ask a different question: “Can I have every book from Harbor Press?” We have drawers by title, by subject and by author. Is there a drawer by publisher? No. So what does the librarian do? Walks every aisle and opens every book. That’s a table scan, and the three drawers we paid for didn’t help at all. ⟲

In Salesforce it’s the same. Filter on a field that isn’t indexed, and the indexes on the other fields can’t help that filter. That’s why the last module’s tables matter: before you worry about anything else, check whether the field you filter on has an index, standard or custom. That’s the first of the three: is the nitro installed? ⟲

A drawer for the question, or none Three areas, left to right: the question, the index's drawers of cards, and the shelves. Step 1: someone asks for Plumbing Basics, by title, tagged fast. A navy arrow labelled find the card goes to the card by title (Plumbing Basics, shelf B4), and a second navy arrow labelled get the book, fast, goes to the book on shelf B4. Step 2: the question becomes books from Harbor Press, by publisher, tagged slow. An arrow labelled ask the drawers ends at a greyed card, no drawer by publisher, tagged no match. A second arrow labelled go around the index, slow, runs under the drawers to a card on the shelves, walk every shelf, open every book one by one, tagged slow, and every book is tagged opened: Plumbing Basics (Harbor Press, shelf B4), Garden Design (North Field, shelf A1) and Home Wiring (Harbor Press, shelf C7). Underneath: by title, the drawer takes you straight to the book, fast; by publisher, there's no drawer, so no match in the index; go around it and open every book, a table scan, slow. THE QUESTION THE INDEX: CARDS IN DRAWERS THE SHELVES “Plumbing Basics”, please by title fast Card by title Plumbing Basics → shelf B4 Card by subject home repair → shelf B4 Card by author A. Ortiz → shelf B4 Plumbing Basics publisher: Harbor Press · shelf B4 Garden Design publisher: North Field · shelf A1 Home Wiring publisher: Harbor Press · shelf C7 find the card get the book: fast By title, the drawer takes you straight to the book: fast. By publisher, there's no drawer, so no match in the index; go around it and open every book: a table scan, slow. HOW TO READ IT someone asking a card: one fact and the shelf a book on its shelf © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

By title, there’s a drawer: find the card, get the book. Fast. ⟲

A drawer for the question, or none Three areas, left to right: the question, the index's drawers of cards, and the shelves. Step 1: someone asks for Plumbing Basics, by title, tagged fast. A navy arrow labelled find the card goes to the card by title (Plumbing Basics, shelf B4), and a second navy arrow labelled get the book, fast, goes to the book on shelf B4. Step 2: the question becomes books from Harbor Press, by publisher, tagged slow. An arrow labelled ask the drawers ends at a greyed card, no drawer by publisher, tagged no match. A second arrow labelled go around the index, slow, runs under the drawers to a card on the shelves, walk every shelf, open every book one by one, tagged slow, and every book is tagged opened: Plumbing Basics (Harbor Press, shelf B4), Garden Design (North Field, shelf A1) and Home Wiring (Harbor Press, shelf C7). Underneath: by title, the drawer takes you straight to the book, fast; by publisher, there's no drawer, so no match in the index; go around it and open every book, a table scan, slow. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.39.0", "built": "2026-10-21", "id": "DGM-DA-QR-16", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "cells 0.1.0", "theme": "stevetecharc 1.4.0"} THE QUESTION THE INDEX: CARDS IN DRAWERS THE SHELVES Books from Harbor Press? by publisher slow Card by title Plumbing Basics → shelf B4 Card by subject home repair → shelf B4 Card by author A. Ortiz → shelf B4 No drawer by publisher nothing to find no match Plumbing Basics publisher: Harbor Press · shelf B4 opened Garden Design publisher: North Field · shelf A1 opened Home Wiring publisher: Harbor Press · shelf C7 opened Walk every shelf open every book, one by one slow ask the drawers go around the index: slow By title, the drawer takes you straight to the book: fast. By publisher, there's no drawer, so no match in the index; go around it and open every book: a table scan, slow. HOW TO READ IT someone asking a card: one fact and the shelf a book on its shelf © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

By publisher, there’s no drawer: no match in the index. Go around it and open every book: a table scan. Slow. ⟲

Asking the Index the Right Way

Section intitulée « Asking the Index the Right Way »

Now say there is a drawer. The title drawer, sorted A to Z. Which questions can it answer quickly? ⟲

  • Find the book titled Plumbing Basics. Go to P, find the card. Fast. ⟲
  • Find every book whose title starts with “Plumbing”. Go to P, read the cards that start with “Plumbing”, stop when they don’t. Fast. ⟲
  • Find these three titles. Three trips to the drawer. Fast. ⟲
  • And with a drawer of cards by date, find every book added this year: go to the first card for this year and read until the year ends. Fast. ⟲

Each of those questions tells the librarian where to start in the drawer. Now some questions the same drawer can’t answer that way: ⟲

  • Find every book whose title contains “Plumbing”. “Advanced Plumbing” is filed under A. “Home Plumbing Guide” is filed under H. The drawer is sorted by how a title starts, so there’s no place in it to start looking. The drawer can’t answer, and the librarian is back to walking every shelf. ⟲
  • Find every book whose title ends with “Basics”. Same problem: the end of a title tells you nothing about where it’s filed. ⟲
  • Find every book that isn’t Plumbing Basics. Nearly every card matches. The drawer narrows nothing. ⟲
  • Find every book with no title. In Salesforce’s drawers, there’s no card for an empty value unless someone asked for one (we saw that in the last module). ⟲

None of these tells the librarian where to start. In class I say it this way: indexes work from left to right. Ask for the start of the value and the index can help. Ask for the middle, the end, or everything except one value, and it can’t: no match in the index, and Salesforce falls back to a table scan. ⟲

Starts with uses the drawer; contains can't Three areas, left to right: the question, the title drawer in A to Z order, and the shelves. The drawer holds Advanced Plumbing (filed under A, shelf C2), Garden Design (G, shelf A1), Home Plumbing Guide (H, shelf A7) and Plumbing Basics (P, shelf B4). Step 1: someone asks for titles that start with Plumbing (LIKE 'Plumbing%'), tagged fast. A navy arrow labelled straight to P, match, goes to the Plumbing Basics card, and a second navy arrow labelled get the book, fast, goes to the book on shelf B4. Step 2: the question becomes titles that contain Plumbing (LIKE '%Plumbing%'), tagged slow. An arrow labelled ask the drawer ends at a greyed card, no way into the drawer, it's sorted by how titles start, tagged no match. A second arrow labelled go around the index, slow, runs under the drawer to a card on the shelves, walk every shelf, open every book one by one, tagged slow, and every book is tagged opened. Underneath: starts with goes straight to P, one card, one book, fast; contains finds no match in the index, so go around it and open every book, a table scan, slow. THE QUESTION THE TITLE DRAWER, A TO Z THE SHELVES Title starts with “Plumbing” LIKE 'Plumbing%' fast Advanced Plumbing filed under A → shelf C2 Garden Design filed under G → shelf A1 Home Plumbing Guide filed under H → shelf A7 Plumbing Basics filed under P → shelf B4 Advanced Plumbing shelf C2 Garden Design shelf A1 Home Plumbing Guide shelf A7 Plumbing Basics shelf B4 straight to P: match get the book: fast Starts with: straight to P, one card, one book: fast. Contains: the drawer is sorted by how titles start, so no match in the index; go around it and open every book: a table scan, slow. HOW TO READ IT someone asking a card, filed by how the title starts a book on its shelf © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

Starts with: straight to P. One card, one book. Fast. ⟲

Starts with uses the drawer; contains can't Three areas, left to right: the question, the title drawer in A to Z order, and the shelves. The drawer holds Advanced Plumbing (filed under A, shelf C2), Garden Design (G, shelf A1), Home Plumbing Guide (H, shelf A7) and Plumbing Basics (P, shelf B4). Step 1: someone asks for titles that start with Plumbing (LIKE 'Plumbing%'), tagged fast. A navy arrow labelled straight to P, match, goes to the Plumbing Basics card, and a second navy arrow labelled get the book, fast, goes to the book on shelf B4. Step 2: the question becomes titles that contain Plumbing (LIKE '%Plumbing%'), tagged slow. An arrow labelled ask the drawer ends at a greyed card, no way into the drawer, it's sorted by how titles start, tagged no match. A second arrow labelled go around the index, slow, runs under the drawer to a card on the shelves, walk every shelf, open every book one by one, tagged slow, and every book is tagged opened. Underneath: starts with goes straight to P, one card, one book, fast; contains finds no match in the index, so go around it and open every book, a table scan, slow. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.39.0", "built": "2026-10-21", "id": "DGM-DA-QR-17", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "cells 0.1.0", "theme": "stevetecharc 1.4.0"} THE QUESTION THE TITLE DRAWER, A TO Z THE SHELVES Title contains “Plumbing” LIKE '%Plumbing%' slow Advanced Plumbing filed under A → shelf C2 Garden Design filed under G → shelf A1 Home Plumbing Guide filed under H → shelf A7 Plumbing Basics filed under P → shelf B4 No way into the drawer it's sorted by how titles start no match Advanced Plumbing shelf C2 opened Garden Design shelf A1 opened Home Plumbing Guide shelf A7 opened Plumbing Basics shelf B4 opened Walk every shelf open every book, one by one slow ask the drawer go around the index: slow Starts with: straight to P, one card, one book: fast. Contains: the drawer is sorted by how titles start, so no match in the index; go around it and open every book: a table scan, slow. HOW TO READ IT someone asking a card, filed by how the title starts a book on its shelf © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

Contains: no way into the drawer, no match in the index. Go around it and open every book: a table scan. Slow. ⟲

Here’s the same list in SOQL: ⟲

Ask it this wayIn SOQLCan the index help?
EqualsName = 'Plumbing Basics'Yes
Starts withName LIKE 'Plumbing%'Yes
One of a short listId IN ('a01…', 'a02…', 'a03…')Yes
A range of dates or numbersCreatedDate = THIS_YEAR, Amount > 10000Yes
ContainsName LIKE '%Plumbing%'No
Ends withName LIKE '%Basics'No
Not equal, not like, not inName != 'Plumbing Basics'No
EmptyRegion__c = null, Name != ''No, by default

That’s not just my rule of thumb. Salesforce lists the cases where a custom index typically isn’t used, including “The filter operator is a negative operator such as NOT EQUAL TO (or !=), NOT CONTAINS, and NOT STARTS WITH” and “You’re comparing with an empty value (Name != ”)” (Working with Very Large SOQL Queries (s'ouvre dans un nouvel onglet), Apex Developer Guide, Winter ‘27). And: “Custom indexes are never used for the following operations: NOT EQUAL TO comparisons, null value comparisons (e.g., Name = ”), leading wildcard searches (e.g., Name LIKE ‘%Smith’)” (Developer Console Query Plan Tool FAQ (s'ouvre dans un nouvel onglet), Help, version 5). ⟲

And if what you really need is to find a word anywhere in the text, remember the two ways to ask from the last module: that’s what FIND and the search engine are built for. Module 5 covers it. ⟲

The Word for It: Selective

Section intitulée « The Word for It: Selective »

So there are two things to check before an index can speed up your query. One: is there an index on the field? Two: are you asking for the data in a way the index can use? Equals, starts with, a short list, a range of dates or numbers: yes. Contains, ends with, not equal, empty: no. ⟲

When you ask the second way, I say you’re asking in a selective manner. That’s the word to learn: selective. A selective filter is one Salesforce can answer through an index instead of walking every record. Reports, list views and SOQL all care about it. ⟲

Salesforce’s own definition has one more part, and it’s the reason this module isn’t over yet: “A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold” (Working with Very Large SOQL Queries (s'ouvre dans un nouvel onglet), Winter ‘27). An index, asked the right way, and not too many records. That’s the second of the three, the pedal all the way down. The third check, the engine in the right range, is where people get into trouble, and it comes next. ⟲

Test knowledge. A filter can use an index only if the field is indexed and you ask in a selective way. Selective: equals, starts with (LIKE 'abc%'), a short IN list, a range on a date or number. Not selective: contains and ends with (a leading %), negative operators (!=, NOT LIKE, NOT IN), and comparisons with an empty value. ⟲

A note on encrypted fields. If a field is encrypted with Shield Platform Encryption, check before you build an important filter on it. Salesforce’s own Well-Architected pages don’t agree today. Trust (s'ouvre dans un nouvel onglet) says probabilistic encryption can’t be used to filter, sort or aggregate, and that deterministic encryption allows exact-match filters. Data Protection Patterns (s'ouvre dans un nouvel onglet) says probabilistic encryption “supports all query operations”. Read both (checked 2026-10-21) and test the filter in your own org. ⟲

Everybody Lives in Arizona: When an Index Won’t Help

Section intitulée « Everybody Lives in Arizona: When an Index Won’t Help »

So you have an index, and you’re asking it the right way: equals, nothing fancy. Is that enough? Not always. Here’s the example I use in class. ⟲

Say we have the addresses of 10 million people from all over the United States, and the state field is indexed. Ask for everyone in Arizona: State = 'AZ'. Arizona is a small slice of the whole country, so the index narrows 10 million records down to a small group. Go to the drawer, pull the Arizona cards, get those records. Fast. That’s the index doing its job. ⟲

Now change the data, not the query. Same field, same index, same State = 'AZ'. But this time everybody lives in Arizona. Every one of the 10 million records matches. The index doesn’t narrow anything. Going to the index first, then fetching 10 million records one card at a time, is actually slower than just reading the whole table. The index isn’t broken. It just isn’t useful for this question. ⟲

And here’s the key: it depends on what you’re looking for. Keep the all-Arizona data and add one person in Texas. Ask for State = 'TX': one match. The index takes you straight to that one record. Fast. Ask for State = 'AZ' on the same data: almost everything matches. Slow. Same field, same index, same data. The only difference is how many records match what you asked for. When a large number of records match what you’re looking for, the index stops helping. ⟲

Watch for this with fields like case status. Status = 'Closed' can be a good filter when you launch, and stop being one as closed cases pile up year after year. We’ll come back to that in The Closed-Cases Trap. ⟲

Everybody lives in Arizona Three areas, left to right: the question, the index on State, and the records, 10 million addresses. Step 1: the people come from all over the US, and someone asks for State = 'AZ', tagged fast. A navy arrow labelled straight to AZ goes to the cards for AZ, a small slice of the cards; a second navy arrow labelled get the records, fast, goes to the Arizona records, a small group. Step 2: everybody lives in Arizona, and the same question, State = 'AZ', is tagged slow. An arrow labelled every card matches goes to the cards for AZ, tagged too many matches; the cards for every other state are empty. A second arrow labelled go around the index, slow, runs under the index to a card, read the whole table, every record one after another, tagged slow, and the Arizona records, all 10 million, are tagged opened. Step 3: the same Arizona data plus one person in Texas, and someone asks for State = 'TX', tagged fast. A navy arrow labelled straight to TX goes to the one card for TX, and a second navy arrow labelled get the record, fast, goes to the one Texas record. Underneath: same field, same index; Arizona out of the whole country, a few cards, fast; Arizona when everybody lives there, every card matches, so reading the table is faster; Texas on that same data, one card, fast; what you're looking for decides. THE QUESTION THE INDEX ON STATE THE RECORDS: 10 MILLION ADDRESSES State = 'AZ' people from all over the US fast Cards for AZ a small slice of the cards Cards for every other state most of the cards Arizona records a small group straight to AZ get the records: fast Same field, same index. Arizona out of the whole country: a few cards, fast. Arizona when everybody lives there: every card matches, so reading the table is faster. Texas on that same data: one card, fast. What you're looking for decides. HOW TO READ IT what you're looking for index cards: the state and where the record is the records © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

People from all over the US: Arizona is a small slice. Fast. ⟲

Everybody lives in Arizona Three areas, left to right: the question, the index on State, and the records, 10 million addresses. Step 1: the people come from all over the US, and someone asks for State = 'AZ', tagged fast. A navy arrow labelled straight to AZ goes to the cards for AZ, a small slice of the cards; a second navy arrow labelled get the records, fast, goes to the Arizona records, a small group. Step 2: everybody lives in Arizona, and the same question, State = 'AZ', is tagged slow. An arrow labelled every card matches goes to the cards for AZ, tagged too many matches; the cards for every other state are empty. A second arrow labelled go around the index, slow, runs under the index to a card, read the whole table, every record one after another, tagged slow, and the Arizona records, all 10 million, are tagged opened. Step 3: the same Arizona data plus one person in Texas, and someone asks for State = 'TX', tagged fast. A navy arrow labelled straight to TX goes to the one card for TX, and a second navy arrow labelled get the record, fast, goes to the one Texas record. Underneath: same field, same index; Arizona out of the whole country, a few cards, fast; Arizona when everybody lives there, every card matches, so reading the table is faster; Texas on that same data, one card, fast; what you're looking for decides. THE QUESTION THE INDEX ON STATE THE RECORDS: 10 MILLION ADDRESSES State = 'AZ' everybody lives in Arizona slow Cards for AZ every card is AZ too many matches Cards for every other state none Arizona records all 10 million opened Read the whole table every record, one after another slow every card matches go around the index: slow Same field, same index. Arizona out of the whole country: a few cards, fast. Arizona when everybody lives there: every card matches, so reading the table is faster. Texas on that same data: one card, fast. What you're looking for decides. HOW TO READ IT what you're looking for index cards: the state and where the record is the records © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

Everybody lives in Arizona: every card matches. Reading the table is faster. Slow. ⟲

Everybody lives in Arizona Three areas, left to right: the question, the index on State, and the records, 10 million addresses. Step 1: the people come from all over the US, and someone asks for State = 'AZ', tagged fast. A navy arrow labelled straight to AZ goes to the cards for AZ, a small slice of the cards; a second navy arrow labelled get the records, fast, goes to the Arizona records, a small group. Step 2: everybody lives in Arizona, and the same question, State = 'AZ', is tagged slow. An arrow labelled every card matches goes to the cards for AZ, tagged too many matches; the cards for every other state are empty. A second arrow labelled go around the index, slow, runs under the index to a card, read the whole table, every record one after another, tagged slow, and the Arizona records, all 10 million, are tagged opened. Step 3: the same Arizona data plus one person in Texas, and someone asks for State = 'TX', tagged fast. A navy arrow labelled straight to TX goes to the one card for TX, and a second navy arrow labelled get the record, fast, goes to the one Texas record. Underneath: same field, same index; Arizona out of the whole country, a few cards, fast; Arizona when everybody lives there, every card matches, so reading the table is faster; Texas on that same data, one card, fast; what you're looking for decides. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.39.0", "built": "2026-10-21", "id": "DGM-DA-QR-21", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "cells 0.1.0", "theme": "stevetecharc 1.4.0"} THE QUESTION THE INDEX ON STATE THE RECORDS: 10 MILLION ADDRESSES State = 'TX' everybody in Arizona, plus one in Texas fast Cards for AZ every card but one Card for TX one card Arizona records all but one The one Texas record one record straight to TX get the record: fast Same field, same index. Arizona out of the whole country: a few cards, fast. Arizona when everybody lives there: every card matches, so reading the table is faster. Texas on that same data: one card, fast. What you're looking for decides. HOW TO READ IT what you're looking for index cards: the state and where the record is the records © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

The same data, one person in Texas: one card. Fast. What you’re looking for decides. ⟲

Why Too Many Matches Is Slower

Section intitulée « Why Too Many Matches Is Slower »

Think about the library again. You want one book: find the card, walk to the shelf, done. Now you want 9 million of the 10 million books, and you insist on using the card catalog. Find a card, walk to the shelf, come back to the drawer, next card, walk to another shelf, come back… nine million times. It would be faster to start at the first aisle and walk the shelves once, in order. ⟲

Salesforce builds that limit into the index itself: “The index table places upper limits on the number of records that an indexed search can effectively return” (Indexes (s'ouvre dans un nouvel onglet), Winter ‘27). So before using an index, Salesforce counts: how many records would this filter match? If the answer is a small part of the object, the index is worth it. If the matches rise above a certain percentage of the records, Salesforce skips the index and reads the table. ⟲

That percentage has a name: the selectivity threshold. Remember Salesforce’s definition of a selective query: an indexed field, asked the right way, and a filter that “reduces the resulting number of rows below a system-defined threshold”. This is that threshold, and it’s the third check. Let’s look at the numbers. ⟲

Les seuils

Section intitulée « Les seuils »

Why does the threshold matter so much? Salesforce’s Well-Architected guidance doesn’t soften it: “Query selectivity is the single largest lever on whether a solution scales to millions of records or times out at hundreds of thousands.” And: “Selectivity is capacity you don’t have to buy” (Resource and Cost Optimization (s'ouvre dans un nouvel onglet)). An efficient query leaves room for every other tenant in the building. ⟲

Salesforce sets the line as a percentage of the records, and the percentage depends on the kind of index: one rule for a standard index, a lower one for a custom index (Make Salesforce Platform SOQL Query Selective (s'ouvre dans un nouvel onglet), Help, version 4). Let’s start with the simple case and build up. ⟲

Start Simple: a Standard Index

Section intitulée « Start Simple: a Standard Index »

Take the Name field. It has a standard index. Say we have 100,000 accounts. For a standard index, the line is 30% of the records: 30,000. ⟲

Now ask for Name LIKE 'Steve%'. Steve’s Plumbing, Steve’s Garage, a few others: say 2,000 accounts. 2,000 is under 30,000, so the filter is selective and Salesforce uses the index. Fast. ⟲

Same field, same index, same 100,000 accounts. Now ask for Name LIKE 'S%', every account that starts with S. Say that’s 40,000. 40,000 is over the line. Not selective: Salesforce skips the index and reads the table. ⟲

Under the line, or over it Two columns of small squares, each square 1,000 accounts, 100 squares each, with a dashed line at 30,000, the threshold for a standard index. First column: Name LIKE 'Steve%' matches 2,000, two blue squares at the bottom, under the line; tagged selective. Second column, the same 100,000 accounts: Name LIKE 'S%' matches 40,000, forty amber squares rising over the line; tagged not selective. Underneath: Name has a standard index, so the line is 30% of the records, 30,000 of 100,000; Steve% matches 2,000, under the line, and Salesforce uses the index; S% matches 40,000, over it, and Salesforce reads the table. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.40.0", "built": "2026-10-21", "id": "DGM-DA-QR-22", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "units 0.2.0", "theme": "stevetecharc 1.4.0"} each square = 1,000 accounts · dashed line = the threshold 30,000 2,000 match selective Name LIKE 'Steve%' 100,000 accounts 30,000 40,000 match not selective Name LIKE 'S%' the same 100,000 accounts Name has a standard index, so the line is 30% of the records: 30,000 of 100,000. Steve% matches 2,000, under the line: Salesforce uses the index. S% matches 40,000, over it: Salesforce reads the table. © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

Same index, same records. Under the line, the index is used. Over it, it isn’t. ⟲

Both filters ask in a selective way, a “starts with”. Both use an indexed field. The only difference is how many records match. ⟲

As the Object Grows

Section intitulée « As the Object Grows »

So the line is 30%? For the first million records, yes. After that, Salesforce counts only 15% of the records beyond the first million, and the line never goes above 1 million. In Salesforce’s words, the standard threshold “maxes out at 1 million total targeted records, which you could reach only if you had more than 5.6 million total records” (same source). ⟲

  • 500,000 records: 30% of them, 150,000. ⟲
  • 1 million records: 30% of them, 300,000. ⟲
  • 2 million records: 300,000 for the first million, plus 15% of the second million (150,000): 450,000. ⟲
  • 6 million records: the math would give 1,050,000, but the cap is 1 million. That’s the top end. ⟲
The standard line as the object grows Four columns of small squares, each square 50,000 records; in each, the coloured squares at the bottom are the most records a filter can match and still be selective, under a dashed line, with its number beside it. 500,000 records: 150,000, 30% of them. 1 million records: 300,000, 30%. 2 million records: 450,000, 300,000 plus 15% of the second million. 6 million records: 1 million, tagged the top end, the cap. Underneath: a standard index, 30% of the first million records plus 15% of the records after that, never more than 1 million; the cap is reached only above 5.6 million records. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.40.0", "built": "2026-10-21", "id": "DGM-DA-QR-23", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "units 0.2.0", "theme": "stevetecharc 1.4.0"} each square = 50,000 records · coloured, under the dashed line = the threshold 150,000 150,000 500,000 records 30% of them 300,000 300,000 1 million records 30% of them 450,000 450,000 2 million records 300,000 + 15% of the second million 1 million 1 million the top end 6 million records the cap: never more than 1 million A standard index: 30% of the first million records, plus 15% of the records after that, never more than 1 million. The cap is reached only above 5.6 million records. © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

The line grows with the object, more slowly after the first million, and stops at 1 million. ⟲

Look at what happens to the share. At 1 million records, a filter can match 30% and still be selective. At 2 million, 22.5%. At 6 million, under 17%. The bigger the object, the more a filter has to narrow it down. Salesforce’s Well-Architected guidance puts it the same way: the thresholds “tighten as an object grows past its first million records” (Resource and Cost Optimization (s'ouvre dans un nouvel onglet)). ⟲

Custom Indexes: a Lower Line

Section intitulée « Custom Indexes: a Lower Line »

Now a custom index. Back to State = 'AZ': State isn’t a standard index, so it needs a custom one. A custom index gets a lower line: 10% of the first million records, plus 5% of the records beyond it, never more than 333,333 (same source). ⟲

  • 100,000 records: 10% of them, 10,000. ⟲
  • 1 million records: 10% of them, 100,000. ⟲
  • 6 million records: the math would give 350,000, but the cap is 333,333. The top end. ⟲
A custom index has a lower line Three columns of small squares, each square 10,000 records; in each, the coloured squares at the bottom are the most records a filter can match and still be selective, under a dashed line, with its number beside it. 100,000 records: 10,000, 10% of them. 1 million records: 100,000, 10%. 6 million records: 333,333, tagged the top end, the cap. Underneath: a custom index, such as one on State, 10% of the first million records plus 5% of the records after that, never more than 333,333; on 6 million records, that's under 6%. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.40.0", "built": "2026-10-21", "id": "DGM-DA-QR-24", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "units 0.2.0", "theme": "stevetecharc 1.4.0"} each square = 10,000 records · coloured, under the dashed line = the threshold 10,000 10,000 100,000 records 10% of them 100,000 100,000 1 million records 10% of them 333,333 333,333 the top end 6 million records the cap: never more than 333,333 A custom index, such as one on State: 10% of the first million records, plus 5% of the records after that, never more than 333,333. On 6 million records, that's under 6%. © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

A custom index: a third of the standard line, and a cap of 333,333. ⟲

So on a million contacts, State = 'AZ' is selective only if fewer than 100,000 of them live in Arizona. And on 10 million contacts, the line is still 333,333, about 3.3%. That’s the part people forget: eventually the thresholds stop growing. That’s why large-volume design isn’t “put an index on it.” The question is what will the distribution look like at scale? ⟲

Test knowledge. Know the two thresholds. Standard index: 30% of the first million records, plus 15% of the rest, up to 1 million. Custom index: 10% of the first million, plus 5% of the rest, up to 333,333. Be ready to work one out: a standard index on 2 million records allows 450,000; a custom index on 1 million allows 100,000. ⟲

Le piège des requêtes fermées

Section intitulée « Le piège des requêtes fermées »

L’un de mes exemples préférés, parce que la requête fonctionne parfaitement au lancement. Un filtre sur IsClosed = true. Au début : 100 000 requêtes client, dont 20 000 fermées. Parfait. Le temps passe, et les requêtes client ont une habitude intéressante : elles se ferment. Maintenant, 10 millions de requêtes client, dont 9 millions fermées. Rien n’a changé dans la requête, le champ ou le code. La distribution, si. Le filtre qui réduisait autrefois la population en décrit maintenant presque la totalité. Il est passé de sélectif à non sélectif parce que les données ont vieilli. Une requête peut devenir mauvaise sans que la requête change.

Ajoutez 30 % de croissance

Section intitulée « Ajoutez 30 % de croissance »

Alors je combine la sélectivité avec le Nombre de zéros et la croissance. Si le client n’a pas de projection, j’utilise 30 % par an comme base de planification, pas parce que chaque entreprise grandit de 30 %, mais parce que cela nous donne quelque chose contre quoi concevoir, au lieu de faire comme si le volume d’aujourd’hui était permanent. Partons de 1,5 million d’enregistrements : 1,95 million la deuxième année, 2,54 la troisième, 3,30 la quatrième, 4,28 la cinquième, 5,57 la sixième. À environ 5,6 millions d’enregistrements, le seuil personnalisé non plafonné vaut 100 000 plus 5 % de 4,6 millions, soit 330 000 : pratiquement le plafond de 333 333. À partir de là, les données continuent de grandir et le seuil personnalisé, en pratique, non. Faites ce calcul avant que la production ne vous l’apprenne.

Thirty percent a year, and the custom line Six columns of small squares, each square 25,000 records, one column a year at 30% growth; in each, the coloured squares under a dashed line are the custom-index threshold. Year 1: 1.5 million records, line at 125,000, 8.3%. Year 2: 1.95 million, 147,500, 7.6%. Year 3: 2.54 million, 177,000, 7.0%. Year 4: 3.30 million, 215,000, 6.5%. Year 5: 4.28 million, 264,000, 6.2%. Year 6: 5.57 million, 328,000, 5.9%, tagged near the cap. Underneath: the data grows 30% a year; the custom line grows more slowly and stops at 333,333; today's selective filter can become tomorrow's production problem. {"author": "Steve Simpson", "blueprint": "1.0.0", "builder": "standard 1.40.0", "built": "2026-10-22", "id": "DGM-DA-QR-25", "rights": "\u00a9 2026 Twinshark LLC. All rights reserved.", "template": "units 0.2.0", "theme": "stevetecharc 1.4.0"} each square = 25,000 records · coloured, under the dashed line = the custom threshold 125,000 125,000 Year 1: 1.5 million line 125,000 · 8.3% 147,500 147,500 Year 2: 1.95 million line 147,500 · 7.6% 177,000 177,000 Year 3: 2.54 million line 177,000 · 7.0% 215,000 215,000 Year 4: 3.30 million line 215,000 · 6.5% 264,000 264,000 Year 5: 4.28 million line 264,000 · 6.2% 328,000 328,000 near the cap Year 6: 5.57 million line 328,000 · 5.9% The data grows 30% a year; the custom line grows more slowly and stops at 333,333. Today's selective filter can become tomorrow's production problem. © 2026 Twinshark LLC · stevetecharc.com
Cliquer pour agrandir

Today’s selective filter can become tomorrow’s production problem. ⟲

Two different 30%s. Don’t mix up this 30% with another 30% you’ll meet in Salesforce’s Well-Architected guidance. Reliability (s'ouvre dans un nouvel onglet) says: “Build margin by targeting 70% of governor limits as the operational ceiling under normal conditions, reserving 30% for unexpected spikes.” That 30% is room to spare under the governor limits of a transaction. The 30% here is how fast the data grows each year. Different numbers for different things. And notice how Salesforce describes the 70% figure on another page: “a widely cited community best practice, not an official Salesforce specification” (The Agentic Enterprise (s'ouvre dans un nouvel onglet)). Treat it as good advice, not a rule (pages checked 2026-10-22). And remember: when your main records grow by 30%, the records under them can grow faster. Each new account brings orders, each order brings line items, and every one of them brings activities and history. Child objects and transaction data can multiply the growth, and you can’t always tell which one will grow toward a Salesforce limit first, or past it. So do the math for the children, not only the parents. ⟲

Steve Trucking : la requête d’aujourd’hui, cinq ans plus tard

Section intitulée « Steve Trucking : la requête d’aujourd’hui, cinq ans plus tard »

Lancement avec un million de livraisons ; les coordinateurs filtrent souvent sur Status = 'Scheduled', environ 100 000 enregistrements, 10 %. Raisonnable. Cinq ans plus tard, il y a environ 9 millions d’ordres d’exécution cumulés, mais le nombre de livraisons planifiées à un instant donné dépend de la fenêtre opérationnelle, donc il reste petit. Ce filtre peut même devenir plus sélectif à mesure que l’historique grandit. Prenons maintenant Status = 'Completed' : les enregistrements terminés s’accumulent année après année, et ce filtre réduit de moins en moins bien la population. Même champ, même index, valeur différente, distribution différente, comportement différent. C’est la sélectivité.

Au-delà de l’examen : ce que l’IA change. Un assistant IA écrit WHERE Status = 'Completed' aussi facilement qu’un développeur, et cela fonctionne dans une organisation de test. La production a toujours le même optimiseur, les mêmes index, les mêmes seuils et la même distribution. L’IA n’abolit pas la physique des bases de données. Testez les requêtes générées par l’IA avec un volume et une distribution réalistes, pas avec 500 requêtes client d’exemple.

All Three: the Boost

Section intitulée « All Three: the Boost »

Back to the race. Three things have to be true for the nitro to fire, and now you know all three for a query: ⟲

  1. There’s an index on the field you filter on. (The nitro is installed.) ⟲
  2. You ask in a selective way: equals, starts with, a short list, a range of dates or numbers. (The pedal is all the way down.) ⟲
  3. Not too many records match: you’re under the threshold for that index, today and as the data grows. (The engine is in the right range.) ⟲

All three, and Salesforce goes to the index and your query jumps ahead. Miss any one, and Salesforce reads the table: you’re just driving. And notice that the third one can change on its own. The closed cases pile up, the object grows, and a query that had the boost last year doesn’t have it this year. ⟲

So who checks all three, every time a query runs? Not you, and not the developer. That’s the job of the librarian, and that’s the next module. ⟲

Test knowledge. For an index to be used, three things must be true: the field is indexed, the filter is selective in the way it asks (no leading wildcard, no negative operator, no comparison with an empty value), and the number of matching records is under the selectivity threshold. ⟲

Ce que l’architecte décide ici

Section intitulée « Ce que l’architecte décide ici »
  • Quels filtres à fort volume doivent rester sélectifs aujourd’hui, ainsi qu’en troisième année, en cinquième année et au-delà.
  • Whether the fields those filters use have an index, standard or custom, and whether one is worth its cost. ⟲
  • Whether the filters ask in a selective way, or rely on contains, ends with, not equal or empty values. ⟲
  • Whether the real distribution of values, not just the presence of an index, keeps each filter under its threshold as the data grows. ⟲
  • Which queries, including AI-generated ones, need testing at realistic volume and distribution before production. ⟲

Oui, connaissez les seuils : standard, 30 % du premier million plus 15 % au-delà, plafonné à 1 million ; personnalisé, 10 % plus 5 %, plafonné à 333 333. Mais si c’est tout ce que vous retenez, vous avez manqué la leçon. Le fichier de fiches n’est utile que lorsqu’il économise assez de travail ; un filtre indexé qui renvoie presque tout n’est pas un raccourci ; et le même filtre peut glisser de sélectif à non sélectif à mesure que les données grandissent et vieillissent. Alors, quand quelqu’un dit « ne vous inquiétez pas, ce champ est indexé », posez les trois questions. Puis faites le calcul.

Pour aller plus loin

Section intitulée « Pour aller plus loin »