Questions about Indexes and Queries

.NET SDK
1

I’m looking for a good way to implement a requirement I have. I need to be able to lookup documents that exactly match the three parameters my documents have in the following ways

Site Identifier + UserIdentifier
Site Identifier

SiteIdentifier +UserIdentifier + Type
SiteIdentifier + Type

SiteIdentifier and Type will always be present on these objects, however UserIdentifier is not.

I’m not sure how to best structure my indexes.

I’m currently thinking about and index for each unique query I would need to run. I looked at composite keys, which I would need here and I know I can do an exact query, but is there any way to do it without having 4 indexes? Maybe I am trying to solve a problem I don’t really have.

The use case for this is to be able to delete the keys that are returned. I wrote the same thing using N1QL and got it to work, but I am not sure if using indexes would be faster.

2

@aaronmell

It really depends on the volume of data that you’re going to have. I think I would leans toward creating a single index on Site Identifier. This would allow it to use that index for all 4 queries, and reduce the number of documents the query node needs to pull to only those with that Site Identifier.

So long as there aren’t a large number of documents with that Site Identifier, it should be pretty efficient. If, however, there are lots (read hundreds or thousands) of documents with the same site identifier, then you might need more indexes to get good efficiency. Or if the documents are very large it can affect efficiency as well, I believe.

Also, remember that you can put the EXPLAIN keyword in front of a query and see what indexes the query plan will be using.