Recommendations for Indexing on Subtable (Main) to Improve Performance

[bethany.k]

Are there any best practices or recommendations for setting up indexes on Subtable (Main) to improve performance? For example, would it be beneficial to add an index on nwHeaderID to optimize queries that frequently use this field?

I’m particularly interested in understanding what indexing strategies others have found useful when working with larger datasets in Subtables.

[bethany.k]

I was able to find out the following information…
Indexing Overview in Nextworld
Each table in the platform has four primary sources of indexes:

1. Nextworld Default Indexes

These are automatically created by the system when a table is defined. They include:

• NwHeaderId
• NwExternalId
• NwId

The primary key for each table is built on NwId. Additionally, all default indexes — except for the NwId index — include nwNateId and nwTenantStripe.

2. Auto-Generated Indexes

Visible under the Auto Generated Indexes section on the Indexes tab in the Table Definition.

The system creates these indexes automatically when a field is added to a table with any of the following types:

• Table Lookup
• List Lookup
• Auto Number
• Translatable

Like default indexes, these also include nwNateId and nwTenantStripe automatically (unless the index is a base index).

3. User-Defined Indexes

These are indexes explicitly created by developers in the Table Definition.

Just like system-generated indexes, nwNateId and nwTeanantStripe are automatically added (unless the index is a base index).

Keep in mind that nwID is not included on user defined dexes by default, but could be useful to add in the case of joins. See section Join Indexes.

4. Workflow Indexes

If workflow is enabled for a table, the system automatically creates an index over:

• WorkflowCurrentState
• WorkflowCurrentStateType

Subtable Mains Indexes

To improve performance, the system now automatically adds the following fields to all user-defined and auto-generated indexes on subtable main tables:

• InternalSeq
• NwHeaderId

Additionally, InternalSeq has been added to the NwHeaderId default index. Although default indexes aren’t user-visible, this change still contributes to query performance behind the scenes.

Join Indexes

In the case of table joins, the system automatically creates an index with nwID but its stores as text so it works better in certain situations versus others. For example, it’s helpuful for connecting the tables (joins), but it’s not great at filtering. In those cases, you might still need to create a more specific index that includes the other fields you’re filtering on.

Best Practices & Considerations

Indexing Is Nuanced

Due to the interconnected nature of data in the platform, indexing can be complex. Keep these key points in mind:

• NwHeaderId: Avoid creating unique indexes on this field in Detail tables. A unique index is only appropriate on Header tables or in rare 1:1 header-detail relationships. The standard header-detail pattern assumes a 1-to-many relationship so applying uniqueness at the Detail level contradicts this design. If a 1:1 mapping is required, a different construct should be explored.
• NwId: This is a unique-friendly field and ideal for identifying a single record. It is not included in user defined indexes by default, but including it manually in indexes can boost performance — particularly during JOINs that use a pattern like: (nwTenantStripe, nwNateId, childTable->DataItem = parentTable->NwId)
• When also applying filters (e.g., WHERE childTable->DataItem LIKE ‘bob%’), a compound index such as:
  (nwTenantStripe, nwNateId, NwId, DataItem)
  can enhance query performance by enabling the database to handle both the JOIN and the filter through the same index. This allows the database to use the index for both the JOIN and the filter condition.

Performance Considerations

There are various factors that affect whether a user defined index improves performance, for example, the physical storage size of the index. It’s important to weigh the trade-offs between index depth and performance gains. Adding more fields to an index increases its size, which can negatively impact performance in some cases.

Always test performance both with and without a user defined index to understand its actual impact. Indexes can significantly improve performance but must be evaluated in context.

[cheyanne.schwiethale]

If you are predominantly accessing the information from your subtable records via its parent (i.e. you’re fetching the parent record to access the subtable main information) then the default indexes the platform creates for you on InternalSeq and nwHeaderId are what is going to make the most impact. Those are the only fields the platform currently fetches subtable main records by, so in this use case other indexes won’t have any impact.

On the other hand, if you have a use case where you have built a mini-app (or similar) over a subtable main and are letting users query by their own filter criteria, then defining an index on the subtable that matches their common filtering patterns would be advantageous just like any other table type.

[anleo.pienaar]

Hi @bethany.k thanks for this, I have some concerns regarding #2 Auto-Generated Indexes, perhaps you could please assist?

1. Using SalesOrderDetail as an example, we have 34 extended fields on the table (some are not TL, LL, Auto Numbers etc.) but for the sake of this questions lets assume they are. This will now automatically create 34 separate additional indexes on the table. Is there a setting where the Auto-Generated Indexes are disabled by default? And if so, are there any concerns to weigh up in terms of disabling those?

2. What happens if the expression in the fetch includes both fields from the base table as well as extended fields? Would it not be more performant to add a user-defined index?

3. Similar question to #2 above, but assuming the fetch includes multiple extended fields only - would it not be more performant to create a single user-defined index with the combination of the fields in the expression as opposed to the individual Auto-Generated Indexes?

4. In the past we’ve seen better performance by ensuring the order of the fields in the expression is matched to the index e.g. if I’m fetching and including Field1 & Field2 in the expression, my index should also be in order of Field1 then Field 2. I’m now wondering if the results were perhaps influenced by the Auto-Generated Indexes, and the above may not be true?

Tagging @claire.minnaar

[ross]

Here’s some information, please let me know @anleo.pienaar if you would like more detail on any of the below.

1. There is not a setting that disables auto-generated (AG) indices by default. A developer could go through each of the extension fields that spawn an AG index and disable. Index Query Restriction (IQR) is on for the SalesOrderDetail table, so we disable sorting and filtering on table lookup, list lookup, etc. fields that don’t have either an AG index of their own or a user-created index with that field as the first field. Implication → disabling the AG index means that a user cannot filter or sort using that field without using the advanced filtering.
2. The platform does not merge indices defined on the base table with indices defined on the extension table. If a fetch expression (coming from a logic block or from the UI) includes fields from both the base and extension, the database will pick whichever index will best execute the fetch. If you need to capture base + extension fields in the same common type of query (say, Organizational Unit, Item, Ext1, and Ext2), you can add an index with that set of fields.
3. Whether it’s more performant depends on a variety of factors, but the general answer is probably. A single index capturing all of the fields that sufficiently filter the query should perform better than just a single AG index. This is especially true if the “main” field in the fetch is different than the field on which the fetch is sorted.
4. It’s certainly possible that certain queries used an AG index instead of one you built for the query. The order of the fields in the fetch expression does not matter, but the ordering of the index fields does. Why exactly a certain order will be better than another could be its own conversation, but the top-level field(s) in the index should generally be broad fields that narrow down the result set by some factor - Company, Org Unit, Item, etc. - and the last field(s), if necessary, should be more specific or lend to a sort order - an autonumber, TransactionDateTime, Serial Number. The results of your previous testing could be the result of coincidence with an AG index, or they could be founded because Field2 was a more specific identifier than Field1.