5

I am trying to understand the negative tradeoffs in creating more custom indexes on an sObject. Yes, they are helpful with query selectivity which speeds up reading data, but at what price. Specifically:

  1. What are the tradeoffs in increasing the # of custom indexes on the object?
  2. When you have more indexed fields, are more records getting created on the backend somewhere?
  3. Does having more indexed fields on an object slow down inserts / updates of records? why?
  4. Based on how salesforce search works, is it correct that once the record is inserted/updated it would take longer for the record to be initially indexed?

context: i'm in a LDV environment

2 Answers 2

13

What are the tradeoffs in increasing the # of custom indexes on the object?

Indexes trade read speed for write speed. In other words, the more indexes you have, the faster you can query the data, but the longer it takes to insert/update records.

When you have more indexed fields, are more records getting created on the backend somewhere?

They are not records, in database parlance, they're indexes. The main database is laid out in a grid, like a spreadsheet, with columns being fields and rows being records. Indexes, on the other hand, are typically tree-like structures with each branch leading to various parts of the index. These tree-like structures are balanced in a way that allows an exponentially faster search.

Example:

Suppose we have a database with 100,000,000 records, and you perform a search against those records. Let's say that the word you're looking for is "hope". Without the index, you must individually examine all 100,000,000 records to find just those that match.

Now, we add an index, and let's suppose, for simplicity, there are only three words: help, heat, and hot. Realistically, we've many more, but this text box is only so big. The database might store these indexes as a tree-like structure, like this:

       h
      / \
     e   o
    / \   \
   l   a   t*
  /     \
 p*      t*

Here, we've indexed the words. At the leaf nodes, marked with *, we list every record that matches those words. If we search this index for hope, we follow h-o-?, and find that there's no hop, hope, hoped, or anything else. We did three comparisons to scan 100,000,000 records.

Of course, there are limitations. Null values aren't indexed, so if you query for a null value, you always lose the index option, and certain wildcards will have the same effect. A filter WHERE Name LIKE 'he%' can instantly return help and heat, but a filter WHERE Name LIKE '%eat' has to scan all 100,000,000 records.

As another simple example for illustrative purposes, there's a game where you're asked to find a number between 1 and some upper limit. After each guess, you're told "higher" or "lower," until you find the number.

You can try to brute force this by guessing every number sequentially, or you can perform a binary search. The brute-force method has an average of n/2 guesses, with an worst case scenario of n, where n is the upper limit. A binary search has a worst case scenario of log2(n) (rounded up) guesses.

To put that in practical numbers, choose a random number between 1 and 100. The chosen number is 99. The brute-force method makes 99 guesses. The binary search method chooses 50 ((100+1)/2), 75 ((100+50+1)/2), 88 ((100+75+1)/2), 94 ((100+87+1)/2), 97 ((100+94+1)/2), 99 ((100+97+1)/2), a total of just six guesses. In fact, given 100 items, it can guess the correct number in a guaranteed seven tries. Your calculator should verify this if you type in log2(100).

Similarly, an index fully populated with 100,000,000 million records can find a given number, date, etc. in at most 27 checks, and can find letters in order in basically linear (O(n)) time. Indexes is what gives any database that extra power in finding data quickly. Though, as stated earlier, too many indexes can aggressively pile on write times, so a balance must be struck.

Does having more indexed fields on an object slow down inserts / updates of records? why?

I really all explained this above, but for completeness, every index created means that the database has to seek to the appropriate index entry and write additional data, possibly rebalancing trees, etc. This is nearly as fast as looking up the records, but it involves writing data to more files, so there's a non-zero cost to these index writes. At some point, the indexes might end up costing more than they're worth as it slows down the system further from writing to so many extra files.

Based on how salesforce search works, is it correct that once the record is inserted/updated it would take longer for the record to be initially indexed?

There are two indexes: database indexes and search indexes. Database indexes are performed in real-time, as the records are committed. This guarantees that an index will always find only relevant records by the time the transaction commits. This is why many indexes will slow down insert/update statements.

The search index is asynchronous, though, as it does much more indexing than straight database indexes. Coincidentally, this is why other users sometimes can't find a record created by someone else using global search. Salesforce has a little trick where they can play back recently created records to the user that created them until the index has time to catch up.

At any rate, my apologies on the crash course about databases and indexes, but hopefully this answers your question.

5

In general, indexes speed up read (SELECT) operations, but slow down updates (INSERT, UPDATE). The latter is because as new records are written, each index has to be updated as well as the actual data value. This gets worse the more indexes an object has.

The other factor is that inserting records one at a time is very inefficient. If you can add all your data to a list then only INSERT that into the sObject once, the database code can update each index just once for the whole list, rather than once per record.

[EDIT] You ask for more detail on updating indexes.

A storage object (sObject) is basically a database table, which includes the data, areas for each index, and its metadata. Both data and indexes have to be updated on disk when you INSERT a new record. Index items need to be kept in order, so a new item will be inserted in its place in the index, which may involve moving existing items to make room.

In memory, you might do that with a linked list but that's not efficient on disk as disks read in blocks not individual records, so for each index update, the code might have to read a block, modify it to include the new index item, then write it back. And do that for each index, as well as for the actual data record. Also, if a block becomes full, it has to be split and copied to 2 new blocks.

So an insert of a single record may result in several disk reads and writes. If instead you build a List of all your new data and then INSERT the whole list, the INSERT code can organise all the data and index changes in memory and then update them all together. It will still require multiple disk operations, but as these now account for many records instead of just one it is more efficient. This is why Salesforce encourages you to work with lists and batches where you can.

The other consideration is the number of records in your object. If it's a small object then access should be fast without extra indexes. If however you have millions of records you do need to design your indexes well!

4
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 26, 2022 at 21:59
  • Thanks @David. can you elaborate on what you mean by " The latter is because as new records are written, each index has to be updated as well as the actual data value" I think that's the part that I want to really understand.... I think you hit the nail on the head, that the one must "design indexes well" Sep 26, 2022 at 22:06
  • Done; see updated answer. Sep 27, 2022 at 12:43
  • 1
    P.S. sObject literally means "Salesforce Object", not "storage object." Nice edit, though.
    – sfdcfox
    Sep 27, 2022 at 13:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .