Pagination of search results

Manticore Search returns the top 20 matched documents in the result set by default.

SQL

In SQL, you can navigate through the result set using the LIMIT clause.

LIMIT can accept either one number as the size of the returned set with a zero offset, or a pair of offset and size values.

HTTP JSON

When using HTTP JSON, the nodes offset and limit control the offset of the result set and the size of the returned set. Alternatively, you can use the pair size and from instead.

sql

SELECT  ... FROM ...  [LIMIT [offset,] row_count]
SELECT  ... FROM ...  [LIMIT row_count][ OFFSET offset]

json

{
  "table": "<table_name>",
  "query": ...
  ...
  "limit": 20,
  "offset": 0
}
{
  "table": "<table_name>",
  "query": ...
  ...
  "size": 20,
  "from": 0
}

Result set window

By default, Manticore Search uses a result set window of 1000 best-ranked documents that can be returned in the result set. If the result set is paginated beyond this value, the query will end in error.

This limitation can be adjusted with the query option max_matches.

Increasing the max_matches to very high values should only be done if it's necessary for the navigation to reach such points. A high max_matches value requires more memory and can increase the query response time. One way to work with deep result sets is to set max_matches as the sum of the offset and limit.

Lowering max_matches below 1000 has the benefit of reducing the memory used by the query. It can also reduce the query time, but in most cases, it might not be a noticeable gain.

sql

SELECT  ... FROM ...   OPTION max_matches=<value>

json

{
  "table": "<table_name>",
  "query": ...
  ...
  "max_matches":<value>
  }
}

Scroll Search Option

The scroll search option provides an efficient and reliable way to paginate through large result sets. Unlike traditional offset-based pagination, scroll search offers better performance for deep pagination and provides an easier way to implement pagination. While it utilizes the same max_matches window as offset-based pagination, scroll search can return more documents than the max_matches value by retrieving results over multiple requests using a scroll token. When using scroll pagination, there's no need to use offset and limit together — it's redundant and generally considered overengineering. Instead, just specify a limit along with the scroll token to fetch each subsequent page.

Scrolling via SQL

Initial Query with Sorting Criteria

Start by executing an initial query with your desired sorting criteria. The only requirement is that id must be included in the ORDER BY clause to ensure consistent pagination. The query will return both your results and a scroll token for subsequent pages.

sql

SELECT ... ORDER BY [... ,] id {ASC|DESC};

Example:

sql

SELECT weight(), id FROM test WHERE match('hello') ORDER BY weight() desc, id asc limit 2;

sql

+----------+------+
| weight() | id   |
+----------+------+
|     1281 |    1 |
|     1281 |    2 |
+----------+------+
2 rows in set (0.00 sec)

Retrieving the Scroll Token

After executing the initial query, retrieve the scroll token by executing the SHOW SCROLL command. You must call SHOW SCROLL after every query in the scroll sequence to obtain the updated scroll token for the next page. Each query generates a new token that reflects the latest scroll position.

sql

SHOW SCROLL;

Response:

sql

| scroll_token                       |
|------------------------------------|
| <base64 encoded scroll token>      |

Example:

sql

SHOW SCROLL;

sql

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| scroll_token                                                                                                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBkZXNjLCBpZCBhc2MiLCJvcmRlcl9ieSI6W3siYXR0ciI6IndlaWdodCgpIiwiZGVzYyI6dHJ1ZSwidmFsdWUiOjEyODEsInR5cGUiOiJpbnQifSx7ImF0dHIiOiJpZCIsImRlc2MiOmZhbHNlLCJ2YWx1ZSI6MiwidHlwZSI6ImludCJ9XX0= |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Paginated Query Using scroll

To retrieve the next page of results, include the scroll token in the subsequent query as an option. When the scroll option is provided, specifying the sort criteria is optional. Remember to call SHOW SCROLL again after this query to obtain the new token needed for the following page.

sql

SELECT ... [ORDER BY [... ,] id {ASC|DESC}] OPTION scroll='<base64 encoded scroll token>'[, ...];

This ensures that pagination continues seamlessly, maintaining the sorting context established in the initial query.

Example:

sql

SELECT weight(), id FROM test WHERE match('hello') limit 2
OPTION scroll='eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBkZXNjLCBpZCBhc2MiLCJvcmRlcl9ieSI6W3siYXR0ciI6IndlaWdodCgpIiwiZGVzYyI6dHJ1ZSwidmFsdWUiOjEyODEsInR5cGUiOiJpbnQifSx7ImF0dHIiOiJpZCIsImRlc2MiOmZhbHNlLCJ2YWx1ZSI6MiwidHlwZSI6ImludCJ9XX0=';

sql

+----------+------+
| weight() | id   |
+----------+------+
|     1281 |    3 |
|     1281 |    4 |
+----------+------+
2 rows in set (0.00 sec)

Scrolling via JSON

Initial Request

In the initial request, specify "scroll": true in the options and the desired sorting criteria. Note that id must be present in the sort array. The response will include a scroll token, which can be used for pagination in subsequent requests.

json

POST /search
{
  "table": "<table_names>",
  "options": {
	  "scroll": true
  },

  ...

  "sort": [
    ...
    { "id":{ "order":"{asc|desc}"} }
  ]
}

Example output:

json

{
    "timed_out": false,
    "hits": {

		...

    },
    "scroll": "<base64 encoded scroll token>"
}

json

POST /search
{
  "table": "test",
  "options":
  {
	"scroll": true
  },
  "query":
  {
	"query_string":"hello"
  },
  "sort":
  [
    { "_score":{ "order":"desc"} },
	{ "id":{ "order":"asc"} }
  ],
  "track_scores": true,
  "limit":2
}

json

{
  "took": 0,
  "timed_out": false,
  "hits":
  {
    "total": 10,
    "total_relation": "eq",
    "hits":
	[
      {
        "_id": 1,
        "_score": 1281,
        "_source":
		{
          "title": "hello world1"
        }
      },
      {
        "_id": 2,
        "_score": 1281,
        "_source":
		{
          "title": "hello world2"
        }
      }
    ]
  },
  "scroll": "eyJvcmRlcl9ieV9zdHIiOiJAd2VpZ2h0IGRlc2MsIGlkIGFzYyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI4MSwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoyLCJ0eXBlIjoiaW50In1dfQ=="
}

Paginated Request Using scroll

To continue pagination, include the scroll token obtained from the previous response within the options object of the next request. Specifying the sort criteria is optional.

json

POST /search
{
  "table": "<table_names>",
  "options": {
    "scroll": "<base64 encoded scroll token>"
  },

  ...

}

json

POST /search
{
  "table": "test",
  "options":
  {
	"scroll": "eyJvcmRlcl9ieV9zdHIiOiJAd2VpZ2h0IGRlc2MsIGlkIGFzYyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI4MSwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoyLCJ0eXBlIjoiaW50In1dfQ=="
  },
  "query":
  {
	"query_string":"hello"
  },
  "track_scores": true,
  "limit":2
}

json

{
  "took": 0,
  "timed_out": false,
  "hits":
  {
    "total": 8,
    "total_relation": "eq",
    "hits":
   [
      {
        "_id": 3,
        "_score": 1281,
        "_source":
        {
          "title": "hello world3"
        }
      },
      {
        "_id": 4,
        "_score": 1281,
        "_source":
        {
          "title": "hello world4"
        }
      }
    ]
  },
  "scroll": "eyJvcmRlcl9ieV9zdHIiOiJAd2VpZ2h0IGRlc2MsIGlkIGFzYyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI4MSwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo0LCJ0eXBlIjoiaW50In1dfQ=="
}