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 will not show in the front-end of the system. It has a unique index since every value is unique in the “_id” field.
Types of Indexes
Single field indexes:
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:
Added as one index if multiple fields 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 assist with providing information on how both indexes work on MongoDB:
How to add a single field index to a buffer
To add a single field index to a buffer, follow these instructions:
1. Click the three menu dots (⋮) adjacent to the buffer you are going to add an index on (1). Click on Edit (2) as illustrated in the below image.
2. The below page will appear. Click on the Indexes tab and then click on the highlighted New Index button to add an index.
3. Click on the highlighted plus icon in the below image to add an index.
a) Type the ID field from the dataset to add an index on.
b) Click on the green tick on the far right of the field. 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 click on the Create Index button.
c) Click on the Create Index button to create the index.
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.
1. Follow steps 1 and 2 on how a single field index is created.
2. Add the fields that are being indexed 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 is being queried in the buffer (e.g. the “Type” field first and then the “Status” field). The Create Unique Index check box is left blank since the values are not unique in these fields.
a) 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.
3. Highlighted in the below page is the compound index for the buffer.
For example, 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.