Hi All,
Below query is performing slow.
Select * from bucket where type=“ABC” and start _date<=‘2020-06-29’ and end_date>=‘2020-06-29’
it took around 30 sec.We have 1.2 millions records in bucket and for that type around 16k.
Index is on
Create index idx_endate on Bucket(start_date,end_date) where type=“ABC”
Is there way to tune it more and reduce its execution time.
@Ritz First can you try run explain and figure out where the time is being spent? Also you can try this index
CREATE INDEX adv_type_start_date_end_date ON mybucket(type,start_date,end_date). Also you can use our index advise service https://index-advisor.couchbase.com/
The index you have one of the right index. It depends on how many records that qualified by query and size of each document.
Try switching index keys and see if that helps.
Create index idx1 on Bucket(end_date, start_date) where type=“ABC”;
If that also doesn’t help total size is too big project META().id (so that query uses covering index) and use SDKs get whole document asynchronously.