Best practices for dynamic queries

SQL++
, ,
1

Hello everyone and thanks for the help in advance.

I’m working with a typical scenario where I have a lot of documents with 50+ fields and my team needs to implement a search with multiple selectable filters. Those filters are a subset of the fields, like 20 of them. I’m wondering what is the best approach to solve this problem. We are currently using GSI, but I think that it isn’t feasible to create all possible combination of all the fields for every single case. Do you know any other alternatives, like flex indexes (with search index) or creating a index (GSI) for every single field?

2

You can create flex index and use if it fits for queries Announcing Flex Index With Couchbase - The Couchbase Blog or mix with GSI
create composite index for SLA bound queries. others you can use single field index

1 Like
3

Thanks for the answer. I’m trying the single field index right now and then i will try the others. With the composite index do you mean a single index with all of the fields? Does an index like that work for all the possible combinations of a query?

For example, if i have the documents with fields A, B, C, D, E and I create a index with all of them, should i get good performance regardless of the order and presence of them in my WHERE clause (obv i should put in the index a field that it will be always present like the type of the document)?