Two ideas here -
- Hash index
- Inverted index
#1 Hash Index
This is simply a JSON document intended to make it fast and simple to perform lookups; it’s literally a copy of a collection of items where the key represents a way to directly access the data related to that key.
Let’s say you had a thousand records in a table and you needed to loop through it to find a given record whose name matched a specific customer name. You have three options -
- Perform a FilterByFormula approach for each lookup; ugly, slow, convoluted.
- Loop through every record seeking a match; very slow and CPU-intensive, added code complexity.
- Extract the data from a pre-built hash index; almost instant, elegant.
Approach 3 requires a single pass across all the records containing the customer names (that’s the downside). That pass would create a collection of objects in a single JSON document (ex., oCustomerNameHash that is global to your app) that looks something like this:
{ "ABC Transportation" : {
"customer_id" : "1001",
"customer_contact" : "Jimmy Johns",
other attributes needed for the index...
}
}
When you want to know the contact name for “ABC Transportation”, the code is simple. effortless, and requires only 6 milliseconds.
let customerContact = oCustomerNameHash["ABC Transportation"].customer_contact;
Even if the hash index has 50,000 items in it, the response time will be sub- 20 milliseconds. This single line eliminates a nested loop and the complexity of doing so. It is possible because you performed the loop in advance of needing to perform the lookups. This makes it possible to vastly compress the code and complexity while also sparing the number of loop introspections required to just one pass instead of vastly many passes.
With a hash index like this, you often find multiple cases where oCustomerNameHash can be utilized. Furthermore, you may need multiple hash indexes for the same data. Imagine you wanted to also lookup by customer ID. This would require a new hash index like oCustomerIDHash. This can be built using the same loop so there’s no need to loop for every index you need.
Lastly, you can mix both indexes into one hash index document by simply creating two keys for each customer - one for the customer name, and one for the customer ID. The lookup process is constant.
#2 Inverted Index
This is a very different idea and ascribed to full-text indices. It makes it possible to perform lookups similar to a hash index but it uses an inverted architecture to make it possible to index multiple fields and perform global search for tokens or field-level search for specific tokens.
I believe you want to focus on the hash index for your process.
