If you require a flow to process faster and there are buffers in the flow, adding an index/ indexes or a compound index in buffers will assist in optimizing a buffer’s performance and a flow’s processing time.
Buffer data gets stored on MongoDB. Indexes should be added on key fields that are in a data set. The default “_id” buffer field automatically has an index which won’t show in the front-end of the system. It has a unique index since every value is unique in the “_id” field.
NB: Please note that adding an index only improves a flow’s processing time if you’re querying a buffer which is either by doing upserts, updates, deleting, selecting or using Buffer Lookups. It’s not needed if you’re only inserting.
Single field indexes is when you index single fields if only one field is being used to query a buffer (generally an ID field or a field that you are querying/ filtering on). Many single field indexes can be added.
Compound indexes are added as one index if there are multiple fields that are being used in the same query/ being filtered on in the buffer.
If a Buffer Query step (source or lookup) is being used and it has SQL syntax where many fields are being filtered on in the query, a compound index will need to be created for it. If there is only one field that is being filtered on, then a single field index needs to be added.
The below links assists with providing information on how both indexes work on MongoDB:
How to add a single field index to a buffer:
- Click on the 3 dots circled in red next to the buffer you are going to add an index on. Then click on Edit as illustrated in the below image.
- The below page will appear. Click on the Indexes tab and then click on the highlighted New Index button to add an index.
- Click on the highlighted plus icon in the below image to add an index. Type the ID field from the dataset that you want to add an index on. Then click on the green tick on the far right of the field which is circled in red. Tick the Create unique index check box only if all the values in the indexed field are unique. If there are at least 2 values in the indexed field that are the same, please do not tick the check box or it will show a duplicate key error when you click on the Create Index button. Then click on the Create Index button to create the index.
- The indexed field then gets added to the buffer as illustrated in the below image.
How to add a compound index to a buffer:
Illustrated in the below image is an example of where a compound index would be needed. A Buffer Lookup is being queried/ filtered based on 2 fields.
-
Follow steps 1 and 2 on how a single field index is created.
-
Add the fields that you are indexing on in the same Index page by clicking on the highlighted plus icon in the image below. Add the fields in the same order that you are querying in the buffer with (eg. the “Type” field first and then “Status” field). The Create Unique Index check box is left blank since the values aren’t unique in these fields. Click on the Create Index button once the fields have been added and the green tick on the far right of the field is clicked.
- Highlighted in the below page is the compound index for the buffer.
NB: If the first field in the compound index has a single field index, please delete the single field index by clicking on the bin/ trash can icon.
eg. If the “Type” field had a single field index, it should be deleted after the compound index was added since it is the first field in the compound index. If the “Status” field has a single field index, it does not need to be deleted since it is not the first field that is being queried on and indexed on.
