Limiting results and paging
DSE Search integrates native driver paging with Apache Solr cursor-based paging.
dse.yaml
The location of the dse.yaml file depends on the type of installation:Package installations | /etc/dse/dse.yaml |
Tarball installations | installation_location/resources/dse/conf/dse.yaml |
DSE Search integrates native driver paging with Apache Solr™ cursor-based paging. Pagination, also called cursors, supports using a cursor to scan results. Solr pagination restrictions apply.
Using paging with CQL SELECT
A CQL SELECT run by DSE Search query uses deep paging
when:
Deep paging is not used if a LIMIT is provided, and the LIMIT is less than the
driver's fetch size.
Required | One of these applies |
---|---|
Driver is using paging | No LIMIT is provided. |
Driver is using paging | The number of rows to return is greater than the driver's page size. |
Using paging with CQL Solr queries (solr_query)
Note: When using CQL Solr queries with pagination enabled, your queries
might experience a performance slowdown because Solr is not able to use its query result
cache when pagination is configured. If you do not want to paginate through large result
sets, disable pagination when running CQL Solr queries. See the driver documentation.
In dse.yaml, the cql_solr_query_paging option specifies when to
use pagination:
- To use the driver pagination settings by default when a driver connects to the
database and executes a CQL SELECT statement using a search index
solr_query
option, set the cql_solr_query_paging todriver
. - To enable pagination persistently with CQL Solr queries, set cql_solr_query_paging: on in dse.yaml and restart the node.
-
To dynamically enable paging when cql_solr_query_paging is set to
off
in dse.yaml, set the Solr paging parameter to driver ("paging":"driver"
). For example:SELECT id FROM wiki.solr WHERE solr_query= '{"q":"*", "sort":"id asc", "paging":"driver"}' ;
Note: SearchAnalytics nodes always use driver paging settings. See DSE Analytics and Search integration.
See the documentation for the CQL shell PAGING command and the driver.
Note: If cqlsh PAGING is enabled, query results display in
100-line chunks followed by the more prompt. Press the space bar to move to the next
chunk. When disabled with PAGING OFF, the entire results are displayed.
It is not mandatory to use a sort clause. However, if a sort clause is not provided, sorting is undefined.
Example of CQL SELECT query
SELECT * FROM wiki.solr WHERE id LIKE 'Journal%';
Examples of CQL Solr queries (solr_query)
The wordJournal
is contained in ~159 entries in the
body or title. Use count to determine the number of rows that
match:SELECT count(*) FROM wiki.solr WHERE solr_query = 'Journal';Count returns only a single row; it is not affected by the 10 row limit.
count
-------
159
(1 rows)
Run the
same query without count (and cql_solr_query_paging:
off):
SELECT id FROM wiki.solr WHERE solr_query = 'Journal';Only 10 rows are returned.
id
----------
23759487
23732986
23759527
23759551
23759455
23760810
23731949
23760697
23760871
23738270
(10 rows)
To
return all matching IDs, override the cql_solr_query_paging
setting:
SELECT id FROM wiki.solr WHERE solr_query='{"q":"Journal", "paging":"driver"}';