Index field order by

SQL++
1

hello, I have documents in couchbase with the following format

{
	"entry": [{
		"resource": {
			"id": "20170204397",
			"period": {
				**"start": "2017-11-30"**
			},
			"resourceType": "EpisodeOfCare",
		}
	},
	{
		"resource": {
			"active": true,
			"birthDate": "1988-01-12",
			"gender": "female",
			"id": "00042936",
			"resourceType": "Patient",
		}
	}],
	"id": "20170204397",
	"identifier": {
		"use": "secondary",
		"value": "Episodes"
	},
	"resourceType": "Bundle",
	"type": "collection"
}

I need to create an index for the order by field. This field is period.start where resourceType = EpisodeOfCare

This is the query:

SELECT  ISOFFLINEEPI.*  
FROM `ISOFFLINEEPI` AS ISOFFLINEEPI  
UNNEST ISOFFLINEEPI.entry entryEpisode  
where entryEpisode.resource.resourceType = 'EpisodeOfCare'  
order by entryEpisode.resource.period.`start`  ASC 
LIMIT 10 OFFSET 0

I would like to create an index for entryEpisode.resource.period.start. Would order by take this index?

2

Could you please more specific about what do you mean index for the order by field?
Do you want create index such that query order by uses index order? As this coming from array entry so it is not possible to use query index order.

3

This is my query:

SELECT  ISOFFLINEEPI.*  
FROM `ISOFFLINEEPI` AS ISOFFLINEEPI  
UNNEST ISOFFLINEEPI.entry entryEpisode  
WHERE entryEpisode.resource.resourceType = 'EpisodeOfCare'  
ORDER BY entryEpisode.resource.period.`start`  ASC 
LIMIT 10 OFFSET 0

I would like to create an index for entryEpisode.resource.period.start
Would order by take this index?

The query without order by takes 500 ms and with order by takes 12 s. There are 5000 documents to order.

4

Unnest is self JOIN between array and original document. If your array has multiple “EpisodeOfCare” UNNESTing and sorting on unnest field and projecting original document may result in duplicates. Is that what you want.

This is more complex scenario and there is no easy solution, but you can try this.

create index ix150 on `ISOFFLINEEPI` (ALL ARRAY [entryEpisode.resource.resourceType, entryEpisode.resource.period.`start`] FOR entryEpisode IN entry END);

SELECT DISTINCT RAW META(d).id FROM `ISOFFLINEEPI` AS d UNNEST d.entry AS entryEpisode
WHERE [entryEpisode.resource.resourceType, entryEpisode.resource.period.`start`] >= ["EpisodeOfCare","0001-01-01"] AND [entryEpisode.resource.resourceType, entryEpisode.resource.period.`start`] <= ["EpisodeOfCare","9999-12-31"]
ORDER BY [entryEpisode.resource.resourceType, entryEpisode.resource.period.`start`] ASC
LIMIT 10 OFFSET 0;

Above query generates document id’s then use those document ids to fetch.
Make sure the Unnest Alias and variable in array index exactly same. Check EXPLAIN it should use covered index.

SELECT RAW d 
FROM `ISOFFLINEEPI` AS d USE KEYS ( above sub query);