Search code examples
jsoncouchbaseexplainsql++database-indexes

Indexing in Couchbase - primary index being used instead of secondary index


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",

Solution

  • 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/