REST API Server Index SCAN API

The REST API Server supports index scan operations in addition to primary key read operations. An index scan allows retrieving multiple rows matching filter conditions, optionally using an ordered index to limit the scan range and control the ordering of results.

Example table definition

Assume we have the following table.

CREATE TABLE `tbl` (
  `pk` int NOT NULL,
  `val_1` int DEFAULT NULL,
  `val_2` int DEFAULT NULL,
  `val_3` double DEFAULT NULL,
  `content` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `idx_val` (`val_1`,`val_2`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SCAN API request

POST /0.1.0/DB/TABLE/scan

The scan endpoint exists only under API version 0.1.0.

Body:

{
    "limit": 10,
    "readColumns": [
        {
            "column": "pk"
        },
        {
            "column": "val_1"
        },
        {
            "column": "val_2"
        },
        {
            "column": "content"
        }
    ],
    "filters": {
        "op": "AND",
        "args": [
            {
                "op": "AND",
                "args": [
                    {
                        "op": "ISNOTNULL",
                        "column": "content"
                    },
                    {
                        "op": "CMP",
                        "column": "pk",
                        "cond": "GT",
                        "value": 2
                    }
                ]
            },
            {
                "op": "OR",
                "args": [
                    {
                        "op": "CMP",
                        "column": "val_1",
                        "cond": "LE",
                        "value": 30
                    },
                    {
                        "op": "CMP",
                        "column": "val_2",
                        "cond": "GT",
                        "value": 500
                    }
                ]
            }
        ]
    },
    "index": {
        "name": "idx_val",
        "key_columns": [
            "val_1",
            "val_2"
        ],
        "ranges": [
            {
                "lower": {
                    "values": [
                        10,
                        100
                    ],
                    "inclusive": true
                },
                "upper": {
                    "values": [
                        70,
                        700
                    ],
                    "inclusive": false
                }
            }
        ],
        "order": "asc"
    }
}

SCAN API explanation

The request body consists of 4 parts:

limit (required)

Used to specify the maximum number of rows to return.

readColumns (optional)

Used to specify the columns to be returned.

filters (optional)

Used to define filter conditions.

For example, the filter shown above is equivalent to:

(content IS NOT NULL AND pk > 2) AND (val_1 <= 30 OR val_2 > 500)

Filters are defined using a logical-expression format, which allows applying AND / OR / NAND / NOR operations to exactly two sub-filters. To combine more conditions, nest logical filters. Sub-filters are specified in the args field:

"filters": {"op": "AND | OR | NAND | NOR", "args": []}

Each entry in the args array represents a sub-filter. A sub-filter can either be a comparison filter or another nested logical filter. A filter can be nested at most 32 levels deep; a deeper filter is rejected with 400.

A comparison filter is defined as follows:

{"op": "CMP", "column": "val_1", "cond": "LE", "value": 30}

index (optional)

Used to define an index scan.

The index object consists of the following fields:

name

Specifies the name of the index to be used.

key_columns

Must match the index columns of the specified index. This ensures that the user selects the correct index and provides range values properly. It acts as an additional validation step.

ranges

Specifies the lower and upper bounds for the index scan. An index scan takes at most 64 ranges; more ranges are rejected with 400.

Typically, values for all index columns should be provided. However, partial (prefix) values are supported. For example:

• Providing val_1 = 10 without specifying val_2 is valid, since val_1 is the prefix column of the index.

• Providing val_2 without val_1 is invalid.

order

Indicates whether the returned rows should be ordered by the index, and specifies the order (ASC / DESC).

Example of the response

{
  "data": [
    {
      "pk": 3,
      "val_1": 30,
      "val_2": 300,
      "content": "C5xB5iaZhS1B4LDZtRc1Lp9mN0reX3JpXO5XGCOhh15vsr0q9OnJkZ3FXZs6W9XD"
    },
    {
      "pk": 6,
      "val_1": 60,
      "val_2": 600,
      "content": "NGsifxOOlA70sgoh7n3KJwRve9EKGXbdUwayvsAfZy6rbYlHBqpcxEdJu4rl5Dxn"
    }
  ],
  "rows": 2
}

Slow scans

When Internal.ScanTimingEnabled is true in the configuration of the REST API Server (the default is false), the server times every scan. A scan that takes at least Internal.SlowScanThresholdUs microseconds (default 10000) is recorded in a circular buffer that keeps the last Internal.SlowScanBufferSize slow scans (default 1000). Without ScanTimingEnabled, no scans are recorded.

GET /0.1.0/slow-scans returns the recorded slow scans, each with its total time, the time spent in each phase of the scan, the database, table and index, the limit and the number of rows fetched. It also returns statistics over all timed scans: their number, the average, the 80th, 90th, 95th, 99th and 100th percentiles, and a histogram of the scan times.

DELETE /0.1.0/slow-scans clears the recorded slow scans and the statistics.