Optional index keys

SQL++
1

Hello,

I currently have an index on 4 different properties:

CREATE INDEX test ON default(docType, brandId, missionId, status) WHERE docType="Submission".

In my particular use case, sometimes the filter might occur (excluding the docType) in the 1st, 2nd, 3rd property, other times in the 2nd and 3rd and, occasionally, just on the 3rd. Is there a way I can handle every scenario with a single index, or would a better approach be to create 3 different indexes for each use case?

My current installation is Couchbase 4.6.1. Thank you

2

You can create different indexes if you want cover the query and better performance,
Or you can create individual key index or adaptive index https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/adaptive-indexing.html

1 Like
3

Any reason why you’re carrying docType in the index if you are filtering on it as well?
You should be able to infer it.

4

I am not sure I understand correctly what you meant. Do you mean building the index without the docType and making the query without the docType as well?

5

You could create the index without docType in it, since it will always be the same.

CREATE INDEX test ON default(brandId, missionId, status) WHERE docType=“Submission”;

6

Considering there is no other combination except these, I guess following index should satisfy all these three conditions:

CREATE INDEX test ON default( status, missionId, brandId ) WHERE docType=“Submission”.

7

But then the following query would no longer apply right?

SELECT *
FROM default
WHERE docType="Submission"
AND brandId="1" AND missionId="1" AND status="1"

The reason why I ask is because we have an custom engine that uses a query template for N1QL queries (and always injects the docType), and changing that would be costly for us at this point

8

I thought about this approach, but I was under the impression that when building an index we should try to use more restrictive parameters first.

So in the context of a brand, it would not make sense to have the index in status first because it would contain all submissions of all brands in a first step, hence the brandId being more restrictive, but perhaps I am not thinking correctly.

9

@vsr1 can tell you better. But I guess the position shouldn’t matter as long as you have the leading key of the index in where clause.

1 Like
10

This has been in my thoughts lately. I need an answer for this as well :wink:
@vsr1/@clinton1ql, please make this clear.

11

can you try the query by changing the order of index and post the execution time in both cases?

12

In this case doesn’t matter because all of them are equality predicates. If it is not equality predicate it makes huge difference .

Keeping high selective keys in first makes efficient (Disk I/O computation etc) because you can’t process second key without first so on. https://developer.couchbase.com/documentation/server/current/performance/index_pushdowns.html Composite predicate pushdown shows that.

keeping docType as part of index or not depends. Queries needs have predicate on leading key. If you have all queries have predicate on docType and some don’t have other fields then it may better keep in the index.

https://www.slideshare.net/journalofinformix/couchbase-n1ql-index-advisor slide #31-32

1 Like
13

That query will still work, this index is just smaller. Because you already have docType in the WHERE you don’t need to include it in the index keys.

CREATE INDEX test ON default(docType, brandId, missionId, status) WHERE docType=“Submission”;
vs
CREATE INDEX test ON default(brandId, missionId, status) WHERE docType=“Submission”;

I’m not sure what you mean about the template injecting the docType, I assume that if you have different docTypes you have different indexes for them as well? Since the index here won’t help a query with a WHERE docType = “AnythingElse”

1 Like