I run quite a lot this query in couchbase
SELECT * FROM dev_hostel where data.type = 'Guesthouse'and data.id = '12'
so, I created this index
CREATE INDEX `type-id-index` ON `dev_hostel`(`data.type`,`data.id`)
but when I explain the query I see that the index created is not used, but the primary is used
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan3",
"index": "#primary",
The back ticks are at wrong place. The index must be as follows. As there is no special characters in the fields you can omit back ticks also.
CREATE INDEX `type-id-index` ON `dev_hostel`(data.type,data.id);
FYI: _host is object and kind is field(nested) in object.
You reference as _host.kind or `_host`.`kind`. If you do `_host.kind` it looking field "_host.kind" not sub object.
If you want reference s1 you must use `f1.f2`.s1 because there is dot in the field you must do `f1.f2`.s1
{
"_host": {
"kind": "KIND1",
"id": "ID1"
},
"f1.f2": { "s1": 10}
}
Explore Index advisor https://index-advisor.couchbase.com/