question

The referenced article is a bit out of date, especially if you are using Storage.system.findSlot()/findItem() (it doesn't really address that). Here are some things to note as an addendum to that article:

Indexing Bundle Columns

If you are using Table.query() to query a single global table, and it is a bundle table, you can index certain columns with the right-click menu in the table to improve lookup speed. For example, if your query is SELECT * FROM MyTable WHERE id = 5, then if you have selected one of the right-click "Index this column" options on your bundle table's "id" column, then it should improve speed of queries significantly. The table will keep an index of that column, so that when it sees the clause WHERE id = 5, the index will have stored exactly which rows have id 5, and it will only look at those rows when evaluating the query.

Speed Improvement in 22.0

There is a significant speedup in version 22.0. In previous versions (I think from 20.0 through 21.2), we were too aggressive in trying to use available indexes, such that we were rebuilding indexes that cross reference those indexes, to optimize for the case where there are multiple possible indexes to use (which admittedly doesn't happen that often). In 22.0 we simplified this mechanism, which significantly improved speed. The side effect is that, if there are multiple indexes to use on the same table, it will only use one of them, namely the one that is further left in the query.

Take an example where you have a query like SELECT * FROM MyTable WHERE id = 5 AND quantity = 1, and you have an index on both the id column and the quantity column. In older versions we tried to be clever and filter on both the indexes. However, the result is that you actually have to cross-multiply the two indexes, which kind of defeats the improvement you get from indexing. So in 22.0 we've simplified it so that it will only use one index per table. If there are multiple possible indexes to use, it will use the one left-most in the query, i.e. the id column in this example. This means that, to get the best indexing bang for your buck, you want to put the most limiting indexes to the left of your query's where. If the id column represents a primary key (only one row per id), then that's the most limiting index and should be the left-most in the query.

Requirements for Using an Index

In order to use an index, the filtering expression must be either the main WHERE, or it must be part of an AND in the top-level expression of the WHERE. It cannot be part of an OR, or a "descendant" of an OR.

Examples of where clauses that can use the "id" column's index

• WHERE id = 5
• WHERE id = 5 AND quantity = 1

Examples of where clauses that cannot use the "id" column's index

• WHERE id = 5 OR id = 7 (for this one, you could probably use UNION ALL instead to ensure index usage)
• WHERE quantity = 1 OR (id = 5 and quantity = 2)

Indexes in Inner Joins

For inner joins, the table will see if there is an index, and use it. If there is no index, then it will build a temporary index and use it. The index to use must be on the table on the right side of the join. For example, say your query is SELECT * FROM users INNER JOIN locations ON users.location = locations.id. In this case, the query engine will look to see if there is an index on the locations.id column. If there is not one, then it will build one and run the query. Note that this is different than if your query were SELECT * FROM locations INNER JOIN users ON users.location = locations.id. Even though the results of those two queries may be the same, the speed differences may be significant. Since in the second query, the users table is on the right side of the join, the query engine will look at the users.location column for an index, and build one if there isn't one, meaning potential extra time if you haven't already designated that column as indexed.

Note that this also has significant implications for speed if there are other filters you want to use, because we only use one index per table. For example, say we add a where clause to the above examples: WHERE users.id = 5. Note that, in the first example (users on the left of the inner join, locations on the right), we will look for an index on locations. That leaves us space to also still use the "id" index on users without using two indexes per table. Thus, we could get a very fast total lookup complexity of O(1) (we're looking up the user with id 5, then looking up the location with that user's location). However, if we use the second example (locations on the left, users on the right), we're going to use (or build) an index on the users.location column, which leaves us no room to use the users.id index. Here the complexity could be O(n x m) where n is the number of rows in the locations table, and m is the average number of users per location. So you could potentially get significant speed differences for queries with the same end result.

Using Storage.system.findSlot()/findItem()

The warehousing mechanism uses the same indexing mechanism as described above, but with item/slot labels as the indexes. Note that you should use 22.0 to take advantage of this, as there were significant speed issues with our too aggressive index usage mentioned previously. If you mark an item/slot label as indexed in the storage system properties, then the system will keep an up-to-date index of all item/slot labels in the system, and findSlot()/findItem() with a WHERE clause that filters on those labels will be significantly faster. As of 22.0, the system does not keep indexes on the standard Storage.Slot / Storage.Slot.Item attributes (for example, things like bayID, address, etc.). Thus, if there are standard attributes that you want to be indexed, then you can copy those attributes to labels on the slot/item, so that you can index them. This can be done programmatically:

Storage.system.querySlots("UPDATE Slots SET BayID = slot.bayID") 

(Although it looks like you have to first add the label on all of your slots using the painter first).

Hope this helps