Index Creation Strategy

N1QL
#1

Hi,

I have sample query like this using logic condition in my code
select * from myBucket where year = 2020 AND month =12 (if condition A -> AND A=’’) else if (condition B-> AND B= ‘’)

By this logic, I have actually 2 possible query:

select * from myBucket where year = 2020 AND month =12 AND A = ‘’

select * from myBucket where year = 2020 AND month =12 AND B = ‘’

Do I need to create index with combination year,month,A + year,month,B,
or is there any work around to handle this combination?

THanks

#2

create index ix1 on mybuckt(year,month,A,B);

#3

Thanks @vsr1 for your response.
I will try to implement that.

Another question for complex query using join :

SELECT *
FROM myBucket a
JOIN myBucket b ON KEYS CONCAT(‘B_HIERARCHY::’,a.id)
where a.type_ = ‘A_HIERARCHY’
AND b.type_ = ‘B_HIERARCHY’
and a.number = ‘xxxxxxx’ and b.name like ‘xxx%’ offset 0 limit 10;

And I have created below indexes :

CREATE INDEX idx-myBucket-A_HIERARCHY ON myBucket(number) WHERE (type_ = “A_HIERARCHY”)
CREATE INDEX idx-myBucket-B_HIERARCHY ON myBucket(name) WHERE (type_ = “B_HIERARCHY”)

When I try to hit explain button, it’s only calling the first index only.

How to handle index using join like this?

#4

@Han_Chris1,

That is true. ON KEYS doesn’t need index it uses document look up.

Checkout ANSI joins if you can join on fields.

Also you can use indexer adviser for index recommendations.

https://index-advisor.couchbase.com/indexadvisor/#1