Pagination is a crucial feature for any search engine, allowing users to navigate through large result sets efficiently. Manticore Search offers several powerful pagination methods, each with its own advantages and limitations. This article explores the different pagination options available in Manticore Search, with practical SQL examples to help you implement the best solution for your use case.

Table of Contents

1. Introduction to Pagination
2. Setting Up a Test Environment
3. Traditional Offset-Based Pagination
4. Result Set Window and max_matches
5. Scroll-Based Pagination
6. Pagination via HTTP JSON
7. Sorting Best Practices for Pagination
8. Limitations and Performance Considerations
9. Faceted Search with Pagination
10. Practical Use Cases
11. Conclusion

Introduction to Pagination

When dealing with large datasets, returning all matching results at once is impractical. Pagination solves this by dividing results into manageable chunks or “pages.” Manticore Search provides multiple pagination approaches to accommodate different use cases, from simple list navigation to infinite scrolling interfaces.

In this article, we’ll explore three main pagination methods:

• Traditional offset-based pagination ( Section 3 )
• Deep pagination with max_matches ( Section 4 )
• Scroll-based pagination for efficient navigation through large result sets ( Section 5 )

We’ll also discuss their implementation both via SQL and HTTP JSON interfaces, performance considerations, and practical applications.

Setting Up a Test Environment

Before diving into pagination examples, let’s create a test environment with sample data using the Manticore Load tool. This tool makes it easy to generate and load test data for benchmarking and experimentation:

# Create a products table and populate it with 10,000 records containing "smartphone" in the title
manticore-load --quiet \
  --batch-size=1000 \
  --threads=4 \
  --total=10000 \
  --drop \
  --init="CREATE TABLE products(title text, description text, price int, category string, rating float, stock int)" \
  --load="INSERT INTO products(id, title, description, price, category, rating, stock) VALUES(<increment>, '<text/3/5> smartphone', '<text/20/50>', <int/10/1000>, '<string/5/10>', <float/1/5>, <int/0/100>)"

Output:

With this test environment in place, we can now explore the different pagination methods.

Traditional Offset-Based Pagination

The most straightforward pagination method in Manticore Search uses the LIMIT clause with offset and count parameters. This approach is familiar to anyone who has worked with SQL databases.

Basic Syntax

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

Examples

-- Return the first 5 results (page 1)
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 5;

Output:

+------+----------------------------------------------+-------+----------+
| id   | title                                        | price | weight() |
+------+----------------------------------------------+-------+----------+
| 1179 | Weak spoke? fall. smartphone                 |    10 |     1272 |
| 1388 | 3; her day of. smartphone                    |    10 |     1272 |
| 1636 | Positively bad winter clean. smartphone      |    10 |     1272 |
| 5628 | Foolish from went heard low. smartphone      |    10 |     1272 |
| 8561 | Left sent. Infrequently, try lay. smartphone |    10 |     1272 |
+------+----------------------------------------------+-------+----------+

-- Return results 6-10 (page 2)
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 5, 5;

Output:

+------+----------------------------------------------+-------+----------+
| id   | title                                        | price | weight() |
+------+----------------------------------------------+-------+----------+
|  246 | Soft technically took. smartphone            |    11 |     1272 |
| 1105 | Dirty up are! temporarily called. smartphone |    11 |     1272 |
| 3293 | Instantly thick ran that hurt. smartphone    |    11 |     1272 |
| 3736 | Work her wrong locally. smartphone           |    11 |     1272 |
| 6978 | Stale loud passively sweet clean. smartphone |    11 |     1272 |
+------+----------------------------------------------+-------+----------+

-- Alternative syntax with separate LIMIT and OFFSET
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 5 OFFSET 10;
-- Results 11-15 (page 3)

Output:

+------+----------------------------------------+-------+----------+
| id   | title                                  | price | weight() |
+------+----------------------------------------+-------+----------+
| 7318 | Her spent at. smartphone               |    11 |     1272 |
| 8436 | His? felt work. Angry fast. smartphone |    11 |     1272 |
| 9699 | Feel spend funny. smartphone           |    11 |     1272 |
| 2326 | Quietly wet drew found. smartphone     |    12 |     1272 |
| 4171 | Set perhaps new send soon. smartphone  |    12 |     1272 |
+------+----------------------------------------+-------+----------+

This method is simple to implement and works well for navigating through the initial pages of results. However, it becomes less efficient for deep pagination due to the way Manticore processes queries internally. For details about these limitations, see Section 8: Limitations and Performance Considerations .

Result Set Window and max_matches

By default, Manticore Search limits the number of matches that can be returned to 1000. If you attempt to paginate beyond this limit, the query will result in an error. This limitation can be adjusted using the max_matches option.

Understanding max_matches

The max_matches option controls how many matches Manticore will keep in RAM while searching. It’s designed to limit memory usage per query. The default value is 1000, which is sufficient for most common search scenarios, but you can increase it when you need to access deeper result pages.

-- Increase max_matches to allow deeper pagination (results 1001-1005)
SELECT id, title, price FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 1000, 5 
OPTION max_matches=1500;

Output:

+------+-----------------------------------------+-------+
| id   | title                                   | price |
+------+-----------------------------------------+-------+
| 3550 | Open sold being late wide. smartphone   |   111 |
| 4896 | Grew. Wrote as hungry. smartphone       |   111 |
|  647 | Went seen had. smartphone               |   112 |
|  883 | Hold lose; have thought. smartphone     |   112 |
| 1774 | Get! began sick. Gone, wild. smartphone |   112 |
+------+-----------------------------------------+-------+

-- For very deep pagination (results 5000-5005)
SELECT id, title, price FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 5000, 5 
OPTION max_matches=5500;

Output:

+------+------------------------------------------------+-------+
| id   | title                                          | price |
+------+------------------------------------------------+-------+
| 4894 | Subtly? inside for spend evening. smartphone   |   507 |
| 5203 | Bad; artistically from yearly. smartphone      |   507 |
| 7446 | Empty probably clever! universally. smartphone |   507 |
| 8053 | In! rich daily irregularly. smartphone         |   507 |
| 8055 | Short cold fall; keep physically. smartphone   |   507 |
+------+------------------------------------------------+-------+

Keep in mind that increasing max_matches comes with a memory cost. Each match consumes memory, so setting this value too high can impact server performance, especially under heavy load. For an alternative approach to deep pagination that’s more memory-efficient, see Scroll-Based Pagination .

Scroll-Based Pagination

Scroll-based pagination provides an efficient way to navigate through large result sets. Unlike traditional offset-based pagination, it uses a token to track the current position in the result set, which helps to efficiently navigate through sequential pages of results.

How It Works

1. Execute an initial query with sorting criteria (must include id in ORDER BY)
2. Retrieve a scroll token that encapsulates the current position
3. Use this token in subsequent queries to fetch the next batch of results

Examples

-- Initial query with sorting criteria (must include id in ORDER BY)
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC 
LIMIT 5; SHOW SCROLL\G

Output:

+------+----------------------------------------------+-------+----------+
| id   | title                                        | price | weight() |
+------+----------------------------------------------+-------+----------+
| 1179 | Weak spoke? fall. smartphone                 |    10 |     1272 |
| 1388 | 3; her day of. smartphone                    |    10 |     1272 |
| 1636 | Positively bad winter clean. smartphone      |    10 |     1272 |
| 5628 | Foolish from went heard low. smartphone      |    10 |     1272 |
| 8561 | Left sent. Infrequently, try lay. smartphone |    10 |     1272 |
+------+----------------------------------------------+-------+----------+

*************************** 1. row ***************************
scroll_token: eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBERVNDLCBwcmljZSBBU0MsIGlkIEFTQyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6InByaWNlIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoxMCwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo4NTYxLCJ0eXBlIjoiaW50In1dfQ==

-- Subsequent paginated query using the scroll token
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC 
LIMIT 5 
OPTION scroll='eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBERVNDLCBwcmljZSBBU0MsIGlkIEFTQyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MywidHlwZSI6ImludCJ9LHsiYXR0ciI6InByaWNlIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo1MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo0NCwidHlwZSI6ImludCJ9XX0=';

Output:

+------+----------------------------------------------+-------+----------+
| id   | title                                        | price | weight() |
+------+----------------------------------------------+-------+----------+
| 1179 | Weak spoke? fall. smartphone                 |    10 |     1272 |
| 1388 | 3; her day of. smartphone                    |    10 |     1272 |
| 1636 | Positively bad winter clean. smartphone      |    10 |     1272 |
| 5628 | Foolish from went heard low. smartphone      |    10 |     1272 |
| 8561 | Left sent. Infrequently, try lay. smartphone |    10 |     1272 |
+------+----------------------------------------------+-------+----------+

Scroll-based pagination is particularly well-suited for implementing “Load More” or infinite scroll functionality in web applications. For a complete implementation example, see Practical Use Cases .

Pagination via HTTP JSON

In addition to SQL, Manticore Search offers pagination via HTTP JSON. This is particularly useful for web applications or microservice architectures. Both traditional offset-based pagination and scroll-based pagination are supported through the JSON API.

Traditional Offset-Based Pagination via HTTP JSON

The HTTP JSON API uses the offset and limit parameters to control pagination. This is equivalent to the SQL LIMIT offset, count syntax.

Basic Syntax

{
  "table": "table_name",
  "query": { "match": { ... } },
  "limit": number_of_results,
  "offset": starting_position
}

Example

# First page (results 1-5)
curl -s 0:9308/search -d '{
  "table": "products",
  "query": { "match": { "*": "smartphone" } },
  "limit": 5,
  "offset": 0,
  "sort": [
    { "_score": "desc" },
    { "price": "asc" },
    { "id": "asc" }
  ]
}'|jq .

Output:

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 10000,
    "total_relation": "eq",
    "hits": [
      {
        "_id": 1179,
        "_score": 1272,
        "_source": {
          "title": "Weak spoke? fall. smartphone",
          "description": "Made down never long 3 angry are went asked closed! cold historically got take can to commonly emotionally; socially asked get tame frequently think nowhere? carefully? scientifically small.",
          "price": 10,
          "category": "hkadwpwwk",
          "rating": 2.300000,
          "stock": 63
        }
      },
      // ... more results ...
    ]
  }
}

# Second page (results 6-10)
curl -s 0:9308/search -d '{
  "table": "products",
  "query": { "match": { "*": "smartphone" } },
  "limit": 5,
  "offset": 5,
  "sort": [
    { "_score": "desc" },
    { "price": "asc" },
    { "id": "asc" }
  ]
}' | jq .

Output:

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 10000,
    "total_relation": "eq",
    "hits": [
      {
        "_id": 246,
        "_score": 1272,
        "_source": {
          "title": "Soft technically took. smartphone",
          "description": "This sometimes empty short normally! been send. Locally kind! quickly might infrequently culturally softly became tried quietly good gradually; inside dead loud.",
          "price": 11,
          "category": "wuiymrjdp",
          "rating": 1.600000,
          "stock": 87
        }
      },
      // ... more results ...
    ]
  }
}

Alternative Syntax

The HTTP JSON API also supports the alternative syntax using from and size instead of offset and limit:

{
  "table": "table_name",
  "query": { "match": { ... } },
  "size": number_of_results,
  "from": starting_position
}

This is equivalent to the previous format but follows the Elasticsearch convention, which may be more familiar to some users.

Scroll-Based Pagination via HTTP JSON

The HTTP JSON API also supports scroll-based pagination for more efficient deep pagination. This requires setting up proper sorting criteria and using the scroll token.

Initial Query

curl -s 0:9308/search -d '{
  "table": "products",
  "query": { "match": { "*": "smartphone" } },
  "limit": 5,
  "track_scores": true,
  "sort": [
    { "_score": "desc" },
    { "id": "asc" }
  ],
  "options": {
    "scroll": true
  }
}' | jq .

Output:

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 10000,
    "total_relation": "eq",
    "hits": [
      {
        "_id": 1,
        "_score": 1272,
        "_source": {
          "title": "Incorrectly heavy soft. smartphone",
          "description": "Carefully late sit; put draw afraid; gave hardly been vaguely 1000 this at understood gave always east fake, dull internationally sent. At cold; hot.",
          "price": 540,
          "category": "ogqejby",
          "rating": 2.800000,
          "stock": 52
        }
      },
      // ... more results ...
    ]
  },
  "scroll": "eyJvcmRlcl9ieV9zdHIiOiJAd2VpZ2h0IGRlc2MsIGlkIGFzYyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo1LCJ0eXBlIjoiaW50In1dfQ=="
}

Note the scroll value in the response, which is a base64-encoded token representing your current position in the result set.

Subsequent Queries

curl -s 0:9308/search -d '{
  "table": "products",
  "query": { "match": { "*": "smartphone" } },
  "limit": 5,
  "track_scores": true,
  "options": {
    "scroll": "eyJvcmRlcl9ieV9zdHIiOiJAd2VpZ2h0IGRlc2MsIGlkIGFzYyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo1LCJ0eXBlIjoiaW50In1dfQ=="
  }
}' | jq .

Output:

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 9995,
    "total_relation": "eq",
    "hits": [
      {
        "_id": 6,
        "_score": 1272,
        "_source": {
          "title": "Negatively! it become. smartphone",
          "description": "Be quiet cold nightly poor hourly had thin got dry, informally work, 100 50 needed specifically serious do bring below began! rich loud.",
          "price": 602,
          "category": "hjpwt",
          "rating": 2.500000,
          "stock": 47
        }
      },
      // ... more results ...
    ]
  },
  "scroll": "eyJvcmRlcl9ieV9zdHIiOiJAd2VpZ2h0IGRlc2MsIGlkIGFzYyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoxMCwidHlwZSI6ImludCJ9XX0="
}

Each response includes a new scroll token for the next page, allowing for continued pagination.

Best Practices for HTTP JSON Pagination

1. Include Sort Criteria: For predictable pagination results, always include explicit sorting, e.g.:

   "sort": [
     { "_score": "desc" },
     { "price": "asc" },
     { "id": "asc" }
   ]
   

2. Handle Scroll Tokens Properly: Store the scroll token from each response and use it for the next request.
3. Use Appropriate Error Handling: Check for error responses, especially when paginating deeply.
4. Consider Response Size: Control the amount of data returned by selecting only the needed fields using the _source parameter:

   "_source": ["title", "price", "rating"]
   

5. Monitor Performance: For very large result sets, monitor server performance and consider using scroll-based pagination.

The HTTP JSON pagination methods follow the same principles as their SQL counterparts (described in Sections 3 and 5 ), but with JSON syntax. This makes them ideal for modern web applications and REST APIs.

Sorting Best Practices for Pagination

Proper sorting is crucial for effective pagination, especially for scroll-based pagination. Here are the key considerations:

1. Include a Unique Identifier

For consistent pagination results, always include a unique identifier (typically the id field) in your ORDER BY clause. This ensures that even if multiple documents have the same value for other sorting criteria, the pagination will still produce consistent results.

-- Good: Includes id as the tie-breaker
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC;

-- Bad: No unique identifier, may lead to inconsistent pagination
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY price ASC;

2. Include weight() for Fulltext Search

When using MATCH() in your query, always include weight() in your sorting criteria to ensure the most relevant results appear first:

-- Good: Includes weight() for relevance sorting
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC;

-- Less optimal: Missing relevance sorting
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY price ASC, id ASC;

3. Maintain Sort Order Consistency

When using traditional offset-based pagination, always maintain the same sort order across all paginated queries. Changing the sort order between pages can lead to missed or duplicated results.

-- First page (correct)
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC LIMIT 10;

-- Second page (correct - same sort order)
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC LIMIT 10, 10;

-- Second page (incorrect - different sort order)
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY rating DESC, id ASC LIMIT 10, 10;

4. Use Deterministic Sorting

For scroll-based pagination, always use deterministic sorting criteria. Non-deterministic functions like RAND() should be avoided as they will produce inconsistent results between queries.

-- Good: Deterministic sorting
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC;

-- Bad: Non-deterministic sorting, will break scroll pagination
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY RAND();

5. Sort Direction with ID

When using the id field as a tie-breaker in sorting, you can use either ASC or DESC order, but it must be consistently applied:

-- These are both valid for scroll pagination
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC;
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id DESC;

6. Optimizing Sort Performance

For better sorting performance, consider using the sort_method option:

-- For pre-sorted data (e.g., by id)
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY id ASC 
LIMIT 5 OPTION sort_method=kbuffer;

-- For other sorting criteria
SELECT * FROM products WHERE MATCH('smartphone') ORDER BY weight() DESC, price ASC, id ASC 
LIMIT 5 OPTION sort_method=pq;

The sorting approach you choose can significantly impact pagination performance and consistency. For example, deterministic sorting is essential for scroll-based pagination, while including a unique identifier is crucial for consistent results in all pagination methods. For more details on how sorting affects performance, see Limitations and Performance Considerations .

Limitations and Performance Considerations

Understanding the limitations of different pagination methods will help you choose the right approach for your specific use case.

Offset-Based Pagination Limitations

1. Performance Degrades with Large Offsets: As the offset value increases, query performance decreases. This is because Manticore still needs to process all records up to the offset value before returning the requested page.
2. max_matches Constraint: By default, you can only paginate through the first 1000 results. To go beyond this, you must increase max_matches, which increases memory usage.
3. Memory Usage: Larger max_matches values require more RAM per query, which can impact server performance under heavy load.
4. Consistent Results Not Guaranteed: If documents are added or removed between page requests, you might see duplicates or miss results.

For deep pagination scenarios or implementing “Load More” functionality, consider using Scroll-Based Pagination as described in Section 5 .

Scroll-Based Pagination Limitations

1. Requires ID in Sort Criteria: You must include the id field in your ORDER BY clause, which may not always align with your desired sorting logic.
2. Token Management: You need to store and manage scroll tokens between requests, adding complexity to your application.
3. No Consistency Guarantee: Scroll-based pagination does not guarantee point-in-time consistency. If documents are added, removed, or modified between requests, the results may still be affected.
4. No Random Access: Unlike offset-based pagination, you cannot jump directly to a specific page; you must navigate sequentially.

Faceted Search with Pagination

Faceted search allows users to filter and navigate through search results using multiple dimensions. When combined with pagination, it’s important to understand how pagination affects facet results.

-- Faceted search with pagination (first page)
SELECT id, title, price, weight()
FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 0, 5
FACET category ORDER BY COUNT(*) DESC;

Output:

+------+----------------------------------------------+-------+----------+
| id   | title                                        | price | weight() |
+------+----------------------------------------------+-------+----------+
| 1179 | Weak spoke? fall. smartphone                 |    10 |     1272 |
| 1388 | 3; her day of. smartphone                    |    10 |     1272 |
| 1636 | Positively bad winter clean. smartphone      |    10 |     1272 |
| 5628 | Foolish from went heard low. smartphone      |    10 |     1272 |
| 8561 | Left sent. Infrequently, try lay. smartphone |    10 |     1272 |
+------+----------------------------------------------+-------+----------+
+-----------+----------+
| category  | count(*) |
+-----------+----------+
| ogqejby   |        1 |
| unmfujgqr |        1 |
| ttefm     |        1 |
| hceihdy   |        1 |
| sicjr     |        1 |
| hjpwt     |        1 |
| tvfqyj    |        1 |
| mvdjhbexo |        1 |
| scayuo    |        1 |
| esmlh     |        1 |
| fvbhplj   |        1 |
| lcphmiqmv |        1 |
| lnjfhb    |        1 |
| qexfdulub |        1 |
| tbswa     |        1 |
| eekarf    |        1 |
| airjuod   |        1 |
| ozkbuvgj  |        1 |
| yafbhr    |        1 |
| duccr     |        1 |
+-----------+----------+

-- Faceted search with pagination (second page)
SELECT id, title, price, weight()
FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 5, 5
FACET category ORDER BY COUNT(*) DESC;

Output:

+------+-----------------------------------------------------------+-------+----------+
| id   | title                                                     | price | weight() |
+------+-----------------------------------------------------------+-------+----------+
|  246 | Soft technically took. smartphone                         |    11 |     1272 |
| 1105 | Dirty up are! temporarily called. smartphone              |    11 |     1272 |
| 3293 | Instantly thick ran that hurt. smartphone                 |    11 |     1272 |
| 3736 | Work her wrong locally. smartphone                        |    11 |     1272 |
| 6978 | Stale loud passively sweet clean. smartphone              |    11 |     1272 |
+------+-----------------------------------------------------------+-------+----------+
+-----------+----------+
| category  | count(*) |
+-----------+----------+
| ogqejby   |        1 |
| unmfujgqr |        1 |
| ttefm     |        1 |
| hceihdy   |        1 |
| sicjr     |        1 |
| hjpwt     |        1 |
| tvfqyj    |        1 |
| mvdjhbexo |        1 |
| scayuo    |        1 |
| esmlh     |        1 |
| fvbhplj   |        1 |
| lcphmiqmv |        1 |
| lnjfhb    |        1 |
| qexfdulub |        1 |
| tbswa     |        1 |
| eekarf    |        1 |
| airjuod   |        1 |
| ozkbuvgj  |        1 |
| yafbhr    |        1 |
| duccr     |        1 |
+-----------+----------+

Important Note: Facet results are calculated based on the entire result set matching the query, not just the paginated portion. This means that regardless of which page you’re viewing, the facet counts represent the distribution across all matching documents. The LIMIT clause only affects which documents are returned in the main result set, not the facet calculations. As you can see from the examples above, the facet results are identical for both pages, confirming that they’re calculated from the entire result set.

Pagination Within Facet Results

In addition to paginating the main search results, Manticore Search also allows you to paginate the facet results themselves. This is particularly useful when dealing with a large number of facet values, where displaying all of them might not be practical.

You can use the LIMIT clause within the FACET statement to control the number of facet values returned.

-- Get only the first 5 categories ordered by count
SELECT id, title FROM products 
WHERE MATCH('smartphone') 
LIMIT 3 
FACET category ORDER BY COUNT(*) DESC, category asc LIMIT 0,5;

Output:

+------+---------------------------------------------+
| id   | title                                       |
+------+---------------------------------------------+
|    1 | Incorrectly heavy soft. smartphone          |
|    2 | Know of make afraid foolish. smartphone     |
|    3 | Poor spring evening drove young. smartphone |
+------+---------------------------------------------+
+------------+----------+
| category   | count(*) |
+------------+----------+
| aaaabzwfzn |        1 |
| aabswb     |        1 |
| aacla      |        1 |
| aaejmtubv  |        1 |
| aaethytj   |        1 |
+------------+----------+

You can provide different pagination parameters for different facets in the same query:

-- Multiple facets with different pagination parameters
SELECT id, title FROM products 
WHERE MATCH('smartphone') 
LIMIT 3 
FACET category ORDER BY COUNT(*) DESC, category asc LIMIT 0,5 
FACET category ORDER BY category ASC LIMIT 3,5 
FACET price ORDER BY FACET() ASC LIMIT 5;

Output:

+------+---------------------------------------------+
| id   | title                                       |
+------+---------------------------------------------+
|    1 | Incorrectly heavy soft. smartphone          |
|    2 | Know of make afraid foolish. smartphone     |
|    3 | Poor spring evening drove young. smartphone |
+------+---------------------------------------------+
+------------+----------+
| category   | count(*) |
+------------+----------+
| aaaabzwfzn |        1 |
| aabswb     |        1 |
| aacla      |        1 |
| aaejmtubv  |        1 |
| aaethytj   |        1 |
+------------+----------+
+------------+----------+
| category   | count(*) |
+------------+----------+
| aaejmtubv  |        1 |
| aaethytj   |        1 |
| aaktjgaa   |        1 |
| aalfwcvwil |        1 |
| aaqmumofe  |        1 |
+------------+----------+
+-------+----------+
| price | count(*) |
+-------+----------+
|    10 |        5 |
|    11 |        8 |
|    12 |       10 |
|    13 |        9 |
|    14 |        8 |
+-------+----------+

This example demonstrates several facet pagination techniques:

1. The first facet returns the top 5 categories by count (offset 0, limit 5)
2. The second facet returns categories in alphabetical order, skipping the first 3 (offset 3, limit 5)
3. The third facet returns the first 5 price points ordered by ascending value

Facet pagination is particularly useful for:

• Handling large numbers of facet values (e.g., thousands of product categories)
• Implementing “Show More” functionality for facet values
• Creating dynamic filtering interfaces with the most popular filters shown first
• Performance optimization by limiting the number of facet values returned

Note: Unlike main result pagination, which has performance implications with deep offsets, facet pagination is generally efficient even with large offsets because facet calculations occur after the document matching phase.

Facet Pagination via HTTP JSON

Facet pagination is also available when using the HTTP JSON interface. You can limit the number of facet values returned using the size parameter in the aggregation specification:

{
  "table": "products",
  "query": { "match": { "*": "smartphone" } },
  "limit": 3,
  "aggs": {
    "category_counts": {
      "terms": {
        "field": "category",
        "size": 5
      }
    }
  }
}

This example limits the category facet results to the top 5 values.

Important: Unlike SQL facets, the HTTP JSON interface currently only supports limiting the number of facet values with the size parameter. It does not support the OFFSET parameter for pagination within facets. You can only specify how many results to return, not which ones to skip.

For more details on facet pagination via HTTP JSON, see the Manticore Search documentation .

Practical Use Cases

Pagination with Highlighting

-- Pagination with highlighting
SELECT id, title, price, weight(),
       HIGHLIGHT({limit=100}) as highlight
FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC
LIMIT 5, 5;

Output:

+------+----------------------------------------------+-------+----------+-----------------------------------------------------+
| id   | title                                        | price | weight() | highlight                                           |
+------+----------------------------------------------+-------+----------+-----------------------------------------------------+
|  246 | Soft technically took. smartphone            |    11 |     1272 | Soft technically took. <b>smartphone</b>            |
| 1105 | Dirty up are! temporarily called. smartphone |    11 |     1272 | Dirty up are! temporarily called. <b>smartphone</b> |
| 3293 | Instantly thick ran that hurt. smartphone    |    11 |     1272 | Instantly thick ran that hurt. <b>smartphone</b>    |
| 3736 | Work her wrong locally. smartphone           |    11 |     1272 | Work her wrong locally. <b>smartphone</b>           |
| 6978 | Stale loud passively sweet clean. smartphone |    11 |     1272 | Stale loud passively sweet clean. <b>smartphone</b> |
+------+----------------------------------------------+-------+----------+-----------------------------------------------------+

Implementing “Load More” Button with Scroll Pagination

Scroll-based pagination is ideal for implementing “Load More” functionality, as it provides efficient navigation through large result sets. Here’s how to implement it:

-- Initial query with SHOW SCROLL to get the scroll token
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
ORDER BY weight() DESC, price ASC, id ASC 
LIMIT 5; SHOW SCROLL\G

Output:

+------+------------------------------------------------+-------+----------+
| id   | title                                          | price | weight() |
+------+------------------------------------------------+-------+----------+
| 1179 | Weak spoke? fall. smartphone                   |    10 |     1272 |
| 1388 | 3; her day of. smartphone                      |    10 |     1272 |
| 1636 | Positively bad winter clean. smartphone        |    10 |     1272 |
| 5628 | Foolish from went heard low. smartphone        |    10 |     1272 |
| 8561 | Left sent. Infrequently, try lay. smartphone   |    10 |     1272 |
+------+------------------------------------------------+-------+----------+

*************************** 1. row ***************************
scroll_token: eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBERVNDLCBwcmljZSBBU0MsIGlkIEFTQyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6InByaWNlIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoxMCwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo4NTYxLCJ0eXBlIjoiaW50In1dfQ==
1 row in set (0.00 sec)

-- When user clicks "Load More", use the scroll token to get the next batch
SELECT id, title, price, weight() FROM products 
WHERE MATCH('smartphone') 
LIMIT 5 
OPTION scroll='eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBERVNDLCBwcmljZSBBU0MsIGlkIEFTQyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6InByaWNlIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoxMCwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo4NTYxLCJ0eXBlIjoiaW50In1dfQ=='; SHOW SCROLL\G

Output:

+------+---------------------------------------------+-------+----------+
| id   | title                                       | price | weight() |
+------+---------------------------------------------+-------+----------+
|  246 | Soft technically took. smartphone           |    11 |     1272 |
| 1105 | Dirty up are! temporarily called. smartphone|    11 |     1272 |
| 3293 | Instantly thick ran that hurt. smartphone   |    11 |     1272 |
| 3736 | Work her wrong locally. smartphone          |    11 |     1272 |
| 6978 | Stale loud passively sweet clean. smartphone|    11 |     1272 |
+------+---------------------------------------------+-------+----------+

*************************** 1. row ***************************
scroll_token: eyJvcmRlcl9ieV9zdHIiOiJ3ZWlnaHQoKSBERVNDLCBwcmljZSBBU0MsIGlkIEFTQyIsIm9yZGVyX2J5IjpbeyJhdHRyIjoid2VpZ2h0KCkiLCJkZXNjIjp0cnVlLCJ2YWx1ZSI6MTI3MiwidHlwZSI6ImludCJ9LHsiYXR0ciI6InByaWNlIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjoxMSwidHlwZSI6ImludCJ9LHsiYXR0ciI6ImlkIiwiZGVzYyI6ZmFsc2UsInZhbHVlIjo2OTc4LCJ0eXBlIjoiaW50In1dfQ==

For each subsequent “Load More” click, you would use the new scroll token returned by the previous query. This approach has several advantages over traditional offset-based pagination:

1. Better Performance: No performance degradation with deep pagination
2. No max_matches Limitation: You can efficiently paginate through millions of results
3. Sequential Access: Efficiently navigate through consecutive pages of results

Implementation Tips:

• Store the scroll token in your application’s state (client-side or server-side)
• Include the token in each subsequent request when the user clicks “Load More”
• Always use the most recent token, as each token represents the current position in the result set
• Remember that scroll tokens are temporary and will eventually expire

This implementation approach addresses many of the limitations of traditional offset-based pagination described in Section 8 , particularly for deep pagination scenarios.

Conclusion

Manticore Search offers flexible pagination options to suit different use cases. Traditional offset-based pagination works well for simple interfaces with limited page navigation, while scroll-based pagination provides better performance for deep pagination scenarios like infinite scrolling or data export.

When implementing pagination in Manticore Search:

1. For shallow pagination (first few pages): Use traditional offset-based pagination with consistent sorting.
2. For deep pagination (many pages or large datasets): Use scroll-based pagination with proper id-based sorting criteria.
3. For consistent results: Always include the id field in your ORDER BY clause and maintain consistent sorting criteria across queries.
4. For fulltext search: Include weight() or _score in your sorting criteria to ensure the most relevant results appear first.

By understanding the strengths, limitations, and best practices of each approach, you can implement the most efficient pagination strategy for your specific application needs. Remember that the best pagination method depends on your use case, data volume, and user experience requirements.