In our database every document is related to an user. Every user can have multiple appointments, and each appointment has a startDate and an endDate. I want to do a query on the documents to return the users that has an appointment today. I wonder if you can please suggest a way to make the query run fast.
“firstName”:“my first name”,
“lastName”:“my last name”,
I tried to create the following index:
create index startDate_endDate_index on myBucket
(DISTINCT ARRAY [a.startDate, a.endDate] FOR a IN appoinments)
But that index is not use in my query as the following:
select * from myBucket where (ANY a IN appointments SATISFIES a.startDate<=today and a.endDate>today end)
CREATE INDEX idx_start ON myBucket( DISTINCT ARRAY s.startDate FOR s IN appointments END) WHERE type = "user";
CREATE INDEX idx_end ON myBucket( DISTINCT ARRAY e.startEnd FOR e IN appointments END) WHERE type = "user";
(ANY s IN appointments SATISFIES s.startDate<=today end)
(ANY e IN appointments SATISFIES e.endDate>today end)
(ANY a IN appointments SATISFIES a.startDate<=today and a.endDate>today end)
I tried that and I did an explain on the select query you gave to me. Looks like only idx_start is being used…. Ideally, I want both indices to be used. I wonder if there is any way we can do it with the current couchbase server release.
It should use both indexes. There might be a typo somewhere. Try using only the endDate index by using a USE INDEX clause. After you get that working, remove the USE INDEX clause so that it uses both indexes.
The variable names in query, s and e, must match variable names s and e in the CREATE INDEX statements,