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 @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.