Estimate the read capacity consumption of limit queries - Amazon Keyspaces (for Apache Cassandra)
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Estimate the read capacity consumption of limit queries

When processing a query that uses the LIMIT clause, Amazon Keyspaces reads rows up to the maximum page size when trying to match the condition specified in the query. If Amazon Keyspaces can't find sufficient matching data that meets the LIMIT value on the first page, one or more paginated calls could be needed. To continue reads on the next page, you can use a pagination token. The default page size is 1MB. To consume less read capacity when using LIMIT clauses, you can reduce the page size. For more information about pagination, see Paginate results in Amazon Keyspaces.

For an example, let's look at the following query.

SELECT * FROM my_table WHERE partition_key=1234 LIMIT 1;

If you don’t set the page size, Amazon Keyspaces reads 1MB of data even though it returns only 1 row to you. To only have Amazon Keyspaces read one row, you can set the page size to 1 for this query. In this case, Amazon Keyspaces would only read one row provided you don’t have expired rows based on Time-to-live settings or client-side timestamps.

The PAGE SIZE parameter determines how many rows Amazon Keyspaces scans from disk for each request, not how many rows Amazon Keyspaces returns to the client. Amazon Keyspaces applies the filters you provide, for example inequality on non-key columns or a LIMIT after it scans the data on disk. If you don’t explicitly set the PAGE SIZE, Amazon Keyspaces reads up to 1MB of data before applying filters. For example, if you're using LIMIT 1 without specifying the PAGE SIZE, Amazon Keyspaces could read thousands of rows from disk before applying the limit clause and returning only a single row.

To avoid over-reading, reduce the PAGE SIZE which reduces the number of rows Amazon Keyspaces scans for each fetch. For example, if you define LIMIT 5 in your query, set the PAGE SIZE to a value between 5 - 10 so that Amazon Keyspaces only scans 5 - 10 rows on each paginated call. You can modify this number to reduce the number of fetches. For limits that are larger than the page size, Amazon Keyspaces maintains the total result count with pagination state. In the case of a LIMIT of 10,000 rows, Amazon Keyspaces can fetch these results in two pages of 5,000 rows each. The 1MB limit is the upper bound for any page size set.