Use indexes with JOIN

SQL++
1

Hi !

I have a query with a JOIN:

SELECT COUNT(*) as count FROM DATA_BUCKET event
JOIN DATA_BUCKET patient ON KEYS event.patientId
WHERE 
		event.`type` = "EVENT"
	AND patient.origin = "AS"

And I created an index on the origin attribute:

CREATE INDEX idx_origin ON DATA_BUCKET(origin)

Is it possible to use this index with that query?

Thank’s !

2

yes, try this N1QL

SELECT COUNT(*) AS count 
  FROM DATA_BUCKET patient
  JOIN DATA_BUCKET event ON KEY event.patientId FOR patient
 WHERE event.`type` = "EVENT"
   AND patient.origin = "AS"

with the following index:

CREATE INDEX idx_patientId ON DATA_BUCKET( patientId ) WHERE (type = "EVENT");
CREATE INDEX idx_origin ON DATA_BUCKET( origin ) WHERE origin IS NOT MISSING;

the query plan looks like as following:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "covers": [
          "cover ((`patient`.`origin`))",
          "cover ((meta(`patient`).`id`))"
        ],
        "filter_covers": {
          "cover (((`patient`.`origin`) is not missing))": true
        },
        "index": "idx_origin",
        "index_id": "fa4b5a9576d4e3cc",
        "index_projection": {
          "entry_keys": [
            0
          ]
        },
        "keyspace": "default",
        "namespace": "DATA_BUCKET",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"AS\"",
                "inclusion": 3,
                "low": "\"AS\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexJoin",
              "as": "event",
              "for": "patient",
              "keyspace": "default",
              "namespace": "DATA_BUCKET",
              "on_key": "cover ((`event`.`patientId`))",
              "scan": {
                "covers": [
                  "cover ((`event`.`patientId`))",
                  "cover ((meta(`event`).`id`))"
                ],
                "filter_covers": {
                  "cover ((`event`.`type`))": "EVENT"
                },
                "index": "idx_patientId",
                "index_id": "8a3af42afaaf5595",
                "using": "gsi"
              }
            },
            {
              "#operator": "Filter",
              "condition": "((cover ((`event`.`type`)) = \"EVENT\") and (cover ((`patient`.`origin`)) = \"AS\"))"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(*)"
              ],
              "group_keys": []
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(*)"
        ],
        "group_keys": []
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(*)"
        ],
        "group_keys": []
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "count",
                  "expr": "count(*)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT COUNT(*) AS count \n  FROM DATA_BUCKET patient\n  JOIN DATA_BUCKET event ON KEY event.patientId FOR patient\n WHERE event.`type` = \"EVENT\"\n   AND patient.origin = \"AS\""
}