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?
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)?
If need good performance on any predicate or combination of predicates you should consider FTS index.
FTS index include all the fields you want, internally it use single field inverted index with in FTS it does conjunction/disjunction of those indivudal one. vs GSI it must scan each individual index separately and get to query node and do Instersect or Union
Single index all fields will not work if leading key is not in predicate and may not efficent.