No error while USE INDEX on a missing index

SQL++
1

When executing a query with USE INDEX before 7.6, if the specified index was not found, it used to fallback to an index chosen by the server.

Since 7.6 offers scans, it is the default fallback for queries on keyspace without index.
Since scans have poor performance, I find this extremely dangerous.

Turning off scans entirely could be an option is some cases.

Could it be possible to introduce a flag either at the cluster level or at the client level to instead throw an error if an index is not found ?

2

There is a proposed “FORCE INDEX” feature.

If you run explain on the query, it will return a optimizer_hints.hints_with_error for the invalid index.

explain select * from default:my_bucket.my_scope.my_collection use index( myindex )

[
  {
    "cardinality": 1,
    "cost": 32.910611402923294,
    "optimizer_hints": {
      "hints_with_error": [
        "INDEX(my_collection myindex): Invalid indexes specified: myindex"
      ]
    },
1 Like
3

If USE INDEX is not qualified or not available it specified in explain.
The order is as follows

  1. USE INDEX
  2. Any qualified secondary index
  3. Primary Index
  4. Sequential (KV Range) Scan
  5. Error out

Sequential Scan requires collection Sequential RBAC privilege (This avoid whole collection bring to query node impact cluster performance). Administrator has default all (this) privilege. All others must be granted. Without RBAC privilege same as pre 7.6

The reason it don’t give error when MISSING is avoid unexpected error when index specified not present, not online, index node is down/not reachable etc.

More details DOC-11680

1 Like
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.