Force index usage

SQL++
1

Hi,
I have such data structure:

{
  "rec": [
    {
      "key": "k1",
      "val": {
        "f1": "val1",
        "f2": "val2",
        "f3": "val3",
        "f4": "val4",
        "f5": val5,
        "f6": "val6",
        "f7": val7
      }
    },
    {
      "key": "k2",
      "val": {
        "f1": "val11",
        "f2": "val22",
        "f3": "val33",
        "f4": "val44",
        "f5": val55,
        "f6": "val66",
        "f7": val77
      }
    }
  ]
}

I’ve built such index:

 CREATE INDEX `idx_arr` ON `DATA` ((distinct (array {"f1": ((`v`.`val`).`f1`), "f2": ((`v`.`val`).`f2`), "f3": ((`v`.`val`).`f3`)} for `v` in `rec` end)))

to use it in this query:

SELECT r.val.`f1`,
       r.val.`f2`,
       r.val.`f3`
FROM DATA a USE INDEX (idx_arr)
UNNEST a.rec r

And, although I`ve specified in query to use exactly idx_arr, it still uses primary index.

Why it doesn’t use my index?

2

Index selection is based on predicate (because indexer never index when leading index key is MISSING). Also it validate index hint for qualification to void wrong results. As you don’t have predicate it will not qualify

Pre 7.1

CREATE INDEX `idx_arr` ON `DATA` (ALL ARRAY [r.val.f1,r.val.f2, r.val.f3] for r in `rec` end);

SELECT fltr[0] AS f1, fltr[1] AS f2, fltr[2] AS f3
FROM DATA AS a USE INDEX (idx_arr)
UNNEST a.rec AS r
LET fltr = [r.val.f1,r.val.f2, r.val.f3]
WHERE fltr IS NOT NULL;

7.1+

CREATE INDEX `idx_arr1` ON `DATA` (ALL ARRAY FLATTEN_KEYS(r.val.f1,r.val.f2, r.val.f3) for r in `rec` end);

SELECT r.val.f1, r.val.f2, r.val.f3
FROM DATA AS a USE INDEX (idx_arr1)
UNNEST a.rec AS r
WHERE r.val.f1 IS NOT MISSING
1 Like
3

Thanks it worked, but speed is the same as with using primary index.

We need to be able to quickly get the value of specific fields, and quickly group by fields, and use aggregate functions

Is there a way to increase speed of this query?

SELECT r.val.`f1`,
       r.val.`f2`,
       COUNT(*),
       SUM(r.val.`f3`)
FROM DATA a
LEFT UNNEST a.rec r
WHERE r.val.`f4` = "value"
GROUP BY r.val.`f1`,
         r.val.`f2`
4

original one doesn’t have f4. If you need any arbitrary field you must use FTS search vs GSI index (GSI index order of the index keys are important ).