Considerations while creating index

SQL++
,
1

Hello,

Iam using N1Ql query to retrieve docs from a bucket using a date range filtering.
Dates are stored as epoch timestamp in millis as an array.
{
"_class": ,
“field1”: ,
“field2”: ,
“field3”: [1503294290000,1503380690000],
“field4”: ,
“field5”: ,
“field6”: “”,
“field7”: ,
“field8”: “”,
“field9”: “”
}

Schema used to create index :
CREATE INDEX index-name ON bucket-name(_class,field1,field2,(all (array i for i in field3 end)),field4,field5,field6,field7,field8,field9) WHERE (_class = “class_name”)

Upto field4 is expected in my N1Ql query.
field5,6,7,8,9 are added bcos those fields are required in the final output.

I wanted to know whether is there any critical points to consider while creating an idex like above.
Is there any limit in the number of fields added in indexing?

Or is there any better way to do the same?

2

Remove _class as leading index key because it is equality predicate in index WHERE clause and it will be automatically covered.
Then follow the techniques in the @keshav_m article Right Index Option: How to Get the Right Performance

1 Like
3

Thanks for the quick reply.

From the link Right Index Option: How to Get the Right Performance

Additionally, if a query is referencing only the keys in the index, the query engine will simply answer the query from the index scan result without going to the data nodes. This is a commonly exploited performance optimization.

Since we are retrieving 4000 docs per query, will it be effective to retrieve only keys and then get the data using another query?
Which method is more preferable.

4

You can fetch all required data through N1QL